SQL Server Tables and Referential Integrity

By Mike Aubert, January 9th, 2003 Posted in SQL Server. Subscribe to our RSS Feed

To enforce referential integrity between tables it is possible to setup a foreign key Constraint, or “relationship,” in SQL Server. To demonstrate I have setup two tables Parent and Child. ParentID in the Child table is a foreign key used to reference the child’s parent.

This database diagram shows the two tables (we will learn how to create diagrams in a later article).

In its current form it is possible to have a Child with no Parent.

To setup a relationship and enforce referential integrity:

1. Open one of the tables in the table designer that is going to be part of the relationship (Parent in this example) and click “Manage Relationships…” in the toolbar.

This screen appears.

2. Click New.

3. Set Parent as the “Primary key table” and Child as the “Foreign key table”

4. Under Parent select ID in the first field. Under Child select ParentID. Your screen should look like this.
Before we click Close and save the table let’s review the options at the bottom on this screen (from BOL):

Check existing data on creation - Applies the constraint to data that already exists in the database when the relationship is added to the foreign key table.

• Enforce relationship for replication - Applies the constraint when the foreign key table is copied to a different database. (we will get into replication later)
• Enforce relationship for INSERTs and UPDATEs -Applies the constraint to data that is inserted into, deleted, or updated in the foreign key table. Also prevents a row in the primary key table from being deleted when a matching row exists in the foreign key table.
• Cascade Update Related Fields - Instructs the DBMS to automatically update foreign-key values of this relationship whenever the primary-key value is updated. EX: If this is checked and CustomerID changes from 1 to 2 any foreign-key tables, such as Orders, that reference the CustomerID will be updated to reference the new CustomerID number.
• Cascade Delete Related Fields - Instructs the DBMS to automatically delete rows of the foreign-key table whenever the referred-to row of the primary-key table is deleted. EX: If this is checked and a order is deleted from the Order table any foreign-key rows, such as ones in OrderDetails, that reference the deleted order will also be deleted.

Click Close and Save the table. Note that we are informed saving this table will also save the modifications that needed to be made to other tables (i.e. the Child table was part of the relationship we added).

The database diagram now shows a one-to-many relationship with referential integrity.

Written by Mike Aubert - Visit Website

Go To Page: 1 2 3

All Tutorials by Category:

Entire site Copyright © 1999-2007 2000Trainers.com, all rights reserved.
Content on this site may not be copied or reproduced in any way without permission.

IT Showcase