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
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