Printed 2014/04/18 10:13AM

A Month of PowerShell – Day 15 (Databases: Adding Foreign Keys)

By Wayne Sheffield, 2013/02/15

Welcome to Day 15 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at 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.

Source code   
#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
    $Column = New-Object ("$SMO.Column") ($Table, "LastUpdatedDT", $dtDateTime)
    $Column = New-Object ("$SMO.Column") ($Table, "LastUpdatedBy", [Microsoft.SqlServer.Management.SMO.DataType]::NVARCHAR(100))
    # 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")
    #Add a RowGUID (uniqueidentifier) column:
    $ObjectName = "RowGuid"
    $Column = $Table.Columns[$ObjectName]
    IF (!($Column))
        $Column = New-Object ("$SMO.Column") ($Table, $ObjectName, $dtUniqueI)
    $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")
    # Create the table
    #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.ReferencedTable = "TestTable"
    $FK.ReferencedTableSchema = "Common"
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.

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.