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

2013-02-15 Wayne Sheffield No comments

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.

#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

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.

PowerShell none

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Blogroll

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

Adding a foreign key constraint to a table

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Tags

Blogroll

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

Adding a foreign key constraint to a table

Share this:

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply