Welcome to Day 15 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
Adding a foreign key constraint to a table
Continuing on with the mini-series on tables, today we’re going to add a foreign key constraint to a table. Since we only have one table in our database so far, we’ll create a new table. The foreign key constraint will be created on the unique key in the Common.TestTable – the RowGuid column.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
#Assign variables $Instance = "localhost\SQL2012" $DBName = "PoSh" $SchemaName = "Common" $TableName = "TestTable2" #Assign the SMO class to a variable $SMO = "Microsoft.SqlServer.Management.Smo" #Assign various data types to variables $dtInt = [Microsoft.SqlServer.Management.Smo.Datatype]::Int $stSmallInt = [Microsoft.SqlServer.Management.Smo.Datatype]::SmallInt $dtDateTime = [Microsoft.SqlServer.Management.Smo.Datatype]::DateTime $dtSmallDt = [Microsoft.SqlServer.Management.Smo.Datatype]::SmallDateTime $dtDate = [Microsoft.SqlServer.Management.Smo.Datatype]::Date $dtTime = [Microsoft.SqlServer.Management.Smo.Datatype]::Time $dtBit = [Microsoft.SqlServer.Management.Smo.Datatype]::Bit $dtUniqueI = [Microsoft.SqlServer.Management.Smo.Datatype]::UniqueIdentifier # get the server $Server = New-Object ("$SMO.Server") "$Instance" # assign the database name to a variable $MyDB = $Server.Databases[$DBName] # assign the schema to a variable $Schema = $MyDB.Schemas[$SchemaName] # Check to see if the table exists $Table = $MyDB.Tables.Item($TableName, $SchemaName) IF (!($Table)) { Write-Host "Creating Table: [$SchemaName].[$TableName]" # Get a new table object $Table = New-Object ("$SMO.Table") ($MyDB, $TableName, $Schema.Name) # Get a new identity column object of the integer data type, named the name of the table + "ID" $Column = New-Object ("$SMO.Column") ($Table, "$($TableName)ID", $dtInt) $Column.Identity = $true $Column.IdentitySeed = 1 $Column.IdentityIncrement = 1 # Add this column to the table $Table.Columns.Add($Column) $Column = New-Object ("$SMO.Column") ($Table, "LastUpdatedDT", $dtDateTime) $Table.Columns.Add($Column) $Column = New-Object ("$SMO.Column") ($Table, "LastUpdatedBy", [Microsoft.SqlServer.Management.SMO.DataType]::NVARCHAR(100)) $Table.Columns.Add($Column) # Create the clustered primary key on the identity column $IX = New-Object ("$SMO.Index") ($Table, "PK_$TableName") $IX.IndexKeyType = "DriPrimaryKey" $IX.IsClustered = $true $IX_col = New-Object ("$SMO.IndexedColumn") ($IX, "$($TableName)ID") $IX.IndexedColumns.Add($IX_col) $Table.Indexes.Add($IX) #Add a RowGUID (uniqueidentifier) column: $ObjectName = "RowGuid" $Column = $Table.Columns[$ObjectName] IF (!($Column)) { $Column = New-Object ("$SMO.Column") ($Table, $ObjectName, $dtUniqueI) $Table.Columns.Add($Column) } $ObjectName = "IX_Rowquid" $IX = $Table.Indexes[$ObjectName] IF (!($IX)) { $IX = New-Object ("$SMO.Index") ($Table, $ObjectName) $IX.IndexType = "NonClusteredIndex" $IX.IndexKeyType = "None" $IX.IsClustered = $false $IxCol = New-Object ("$SMO.IndexedColumn") ($IX, "RowGuid") $IX.IndexedColumns.Add($IxCol) $Table.Indexes.Add($IX) } # Create the table $Table.Create() #build a foreign key to Common.TestTable based on the RowGuid column $ObjectName = "FK_$($TableName)" $FK = New-Object ("$SMO.ForeignKey") ($Table, $ObjectName) #Perform the next two statements for each column in the FK $FKCol = New-Object ("$SMO.ForeignKeyColumn") ($FK, "RowGuid", "RowGuid") $FK.Columns.Add($FKCol) $FK.ReferencedTable = "TestTable" $FK.ReferencedTableSchema = "Common" $FK.Create() } cd SQLSERVER:\SQL\localhost\SQL2012\Databases\PoSh\Tables\Common.TestTable2 Get-ChildItem Columns | ` select-object ID, Name, DataType, RowGuidCol, Identity, IdentitySeed, IdentityIncrement, DefaultConstraint |` sort-object ID | ` Format-Table -AutoSize Get-ChildItem ForeignKeys |
Most of this code was creating the second table where the foreign key constraint will be at. Actually building the foreign key is just the last few lines of code – creating a ForeignKey object, creating a ForeignKeyColumn object for each column and assigning it to the ForeignKey.Columns property, setting the ReferencedTable and its Schema, and finally creating the foreign key.
