SQL-DMO
Altering a Table by Adding a FOREIGN KEY Constraint
This example illustrates foreign key definition using the SQL-DMO Key object. In the example, adding the Key object to the Keys collection creates a FOREIGN KEY constraint on the referenced table.
' Create a FOREIGN KEY constraint on the
' Northwind..Products.CategoryID column referencing
' Northwind..Categories.CategoryID.
Dim tableProducts As SQLDMO.Table
Dim keyFKProducts As New SQLDMO.Key
Dim namesFKProducts As SQLDMO.Names
' Get the Products table. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Set tableProducts = _
oSQLServer.Databases("Northwind").Tables("Products")
' Indicate the constrained column in the KeyColumns collection.
keyFKProducts.Type = SQLDMOKey_Foreign
keyFKProducts.KeyColumns.Add "CategoryID"
' Use the ReferencedTable property and ReferencedColumns
' collection to specify constraining values.
keyFKProducts.ReferencedTable = "Categories"
keyFKProducts.ReferencedColumns.Add "CategoryID"
' Mark start of change unit.
tableProducts.BeginAlter
' Add the populated Key object to the Keys collection of the
' Table object.
tableProducts.Keys.Add keyFKProducts
' Create the FOREIGN KEY constraint by committing the unit of change.
tableProducts.DoAlter