SQL Server: Referential Integrity

If you are used to enforcing referential integrity via the “Relationships” panel within Microsoft Access, you will quickly find things are done a little differently within SQL Server.  There are two methods available to help maintain referential integrity within SQL Server.

  1. Foreign Keys / DRI (Declarative Referential Integrity)
  2. Triggers / PRI (Procedural Referential Integrity)
DRI PRI
“Foreign Keys” are declared within the schema of the “Child” table. Custom Code is written and implemented using “Stored Procedures” or “Triggers”.
Quick and Easy to Setup / Configure (using the “Foreign Key” dialog boxes). Harder to Setup / Configure (knowledge of procedural language is required).
Fast Performance. Slower Performance (resources are required to run the procedures).

You’ll probably guess that I’m going to concentrate on the use of DRI (Declarative Referential Integrity), as it is easy to setup and more than adequate for most applications that have a basic requirement to prevent the accidental deletion of key data.

Adding a Foreign Key using SSMS

Before you make any changes to any live / production database, ensure that the data and schema is fully backed up!

Using SSMS (SQL Server Management Studio), connect to the SQL Server Database you wish to work on. When setting up referential integrity, the “Foreign Key” is always added to the “Child” table. So, if you have an “Orders” Table and an “Order Lines” table, the “Foreign Key” should be added to the “Order Lines” Table.

In my chosen example, I have a “Screen Type” Table (Parent) and a “Screens” Table (Child). Select “New Foreign Key” from the “Keys” container within the subordinate table.

Foreign Key 001

The crucial parameters are within the “Tables and Columns Specification” dialog box (shown below). Set the “Primary Key Table” to be the “Parent” table within the relationship (“Screen Type” Table). The “Foreign Key Table” field cannot be amended, as it is the table we are currently working on. Finally, set the column names that link the two tables together. In my example, it is “Screen Type No”.

Foreign Key 002

That’s basically it! The database will no longer permit the removal of records from the “Screen Type” table whilst associated records still exists within the “Screens” table.

Foreign Key 003

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s