SQL Server Tables and Referential Integrity

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

Note that to setup a relationship the following conditions must be true:

• The column from the primary (or parent) table must be the primary key or it must have a unique constraint set on it. (We will cover uniqueness constraints in an upcoming article)
• The related columns in the primary and foreign key tables must have the same data type and must be the same size.
• Both tables must be part of the same database.

Also note that factors such as if the foreign key column allows null or has a unique constraint (again we will cover how to add a unique constraint later) will effect what is and is not allowed in the relationship. For example if I changed ParentID in the Child table to allow nulls, it would be possible to add multiple rows (assuming there is no unique constraint on ParentID in the Child table) that referenced , or “none”, for their ParentID. This is shown in this image.

If the ParentID is anything other than the relationship between the primary/ foreign key tables will be enforced. That is, if a row was added to the above table with ParentID set to 5 instead of a corresponding row with an ID of 5 would have to exist in the Parent table.

Used the following questions to set the properties on the foreign key column:

Must each row in the foreign key table have a corresponding row in the primary key table?

Yes – Uncheck “Allow Null” on the foreign key column

No – Check “Allow Null” on the foreign key column

What type of relationship is this?

one-to-one – Add a unique constraint to the foreign key column

one-to-many – Do not add a unique constraint to the foreign key column

many-to-many – Implement a join table and do not add a unique constraint to either of the two foreign key columns

One last thing, as with anything that you can do in Enterprise Manager…you can also add relationships with SQL statements. See the definitions for CREATE TABLE and ALTER TABLE in the SQL Server Books Online.

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