Add or Drop a SQL Server Database Relationship Using SMO - February 2009

The tip-of-the-month shows how to create a relationship between two SQL Server tables - in the same database. I use relationships to cause child records to be deleted when a parent record is deleted. This prevents the accumulation of orphaned records. The code is contained in class module: cSQLRelationships. Click here to view code that adds relationships to MSAccess tables using ADOX. Note: this code supports adding a relationship for one column. The ADOX example shows how to use multiple columns. This code could be modified to handle multiple columns. I did not have a need for that facility in the application for which this code was created.

Methods Operands
RelationshipAdd otblSMO, sTableNameParent, sColumnName, sColumnReferenced, bCascadeDelete, bCascadeUpdate
RelationshipDrop otblSMO, sTableNameParent
BuildFKName sTableName, sTableNameParent
Operands Description
otblSMO The SMO table for which a relationship is being added/dropped.
sTableName The name of the table for which a relationship is being added/dropped. Used with BuildFKName.
sTableNameParent The name of the table being related to.
sColumnName The name of the column in otblSMO.
sColumnReferenced The name of the column in sTableNameParent.
bCascadeDelete Optional, if true [the default] then cascade record deletions. Eliminate orphaned records.
bCascadeUpdate Optional, if true [the default] then cascade record updates. Rarely needed if GUIDs are used as record keys.
Example Comment
Dim oSQLR As New cSQLRelationships Define an instance of the class
Dim otblSMO as Table = oSMODatabase.Tables("MyChildTable") Define the child table
With oSQLR Reference the class
    .RelationshipDrop(otblSMO, "MyParentTable") Remove the relationship if it exists
    .RelationshipAdd(otblSMO, "MyParentTable", "GUIDParent", "GUID") Add the relationship
End With  
Dim sFK As String = oSQLR.BuildFKName("MyChildTable", "MyParentTable") Build a foreign key name

View Relationships

Use SQL Server Management Studio to view relationships. For example:

Open the Databases collection
Select a Database - Open the Tables collection

Right click on a table name. Select Modify

Right click and select Relationships

View the relationship.

Download VB.Net Code

Click here to download the VS2005 VB.Net source code [2k].

Previous Tip   Tip Index   Next Tip


Updated 2011/04/16