Altering a Table by Adding a FOREIGN KEY Constraint (SQL-DMO)

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

See Also