Maintaining solid relationships is essential
Building and maintaining logical relationships between tables are fundamental parts of working with relational databases. Most databases must maintain certain relationships or the data will be logically corrupt. When such relationships exist, we say that the data has referential integrity. One table is the referenced table and the other is the referencing table; values in the referencing table must match values in the referenced table. (Some people call these tables parent and child tables, but that terminology implies a hierarchy, which the relational model avoids.) SQL Server can automatically enforce referential integrity through foreign key constraints that you define. This feature is called Declarative Referential Integrity (DRI) because it's part of the table definition. You can also use other features, such as triggers, to enforce relationships; this use is procedural referential integrity. In this article, I look at how you can enforce referential integrity in SQL Server, paying particular attention to the relevant new features in SQL Server 2000.
SQL Server 7.0 and earlier releases have only one way to deal with attempted violations of foreign key constraints. If anyone tries to modify data on a table in a way that would violate the referential integrity (as defined through foreign keys), SQL Server disallows that modification and returns an error message. SQL Server 2000 has a new cascade function that can deal with referential integrity violations in another way, as I'll explain.
First, let's look at a quick example to clarify what referential integrity is all about. The Northwind database has a table called Orders and one called Order Details. In the Orders table, the OrderId column is the primary key that uniquely identifies each order. The Order Details table also has an OrderId column, but in this table, the column is a foreign key that must match an existing OrderId in the Orders table. In this example, the Orders table is the referenced table and the Order Details table is the referencing table. If you've set up a foreign key constraint to enforce the relationship between Orders and Order Details, SQL Server verifies that modifying either of those two tables doesn't violate the relationship. For example, if you try to delete a row from the Orders table when the OrderId in that row exists in the Order Details table, the deletion will violate the referential integrity constraint. Trying to update an OrderId column in the Orders table when the original value exists in Order Details but the new value doesn't is also a violation. And, SQL Server must verify every insertion into Order Details to ensure that the new OrderId exists in the Orders table and must verify every update of the OrderId column in Order Details.
Referential Actions
The ANSI SQL-92 standard contains the concept of a referential action. Sometimes, instead of preventing a data-modification operation that would violate a foreign key reference, you might want the database system to perform another, compensating action that allows the modification and still honors the constraint. For example, if you delete an Orders table row that Order Details references, you could instruct SQL Server to automatically delete all related Order Details rows (i.e., cascade the delete to Order Details). That way, you can modify the Orders table without violating the constraint.
The ANSI standard defines four possible referential actions that apply to deletes from or updates to the referenced table: NO ACTION, CASCADE, SET DEFAULT, and SET NULL. The NO ACTION option, which is the ANSI-standard default, prevents the modification. CASCADE allows a delete or update of all matching rows in the referencing table. SET DEFAULT lets the delete or update occur but sets all foreign key values in the referencing table to a default value. And SET NULL allows the delete or update and sets all foreign key values in the referencing table to NULL.
Note that these actions apply only to modifications to the referenced table. When modifying the referencing table, you have only one possible referential integrity action: If you insert or update a value in the foreign key column, the new value must match a value in the referenced table, or SQL Server will reject the modification.
Enforcing Referential Integrity
SQL Server releases before SQL Server 6.0 don't let you declare primary and foreign key relationships in your table definitions. All referential integrity validation must take place through triggers, which you have to code in T-SQL. SQL Server 6.0 introduced constraints, including primary and foreign key constraints, but limits referential actions to NO ACTION. SQL Server 7.0 and 6.5 also offer only the NO ACTION option (without the NO ACTION syntax).
SQL Server 2000 has supported both CASCADE and NO ACTION referential actions since beta 1. Listing 1 shows the syntax for creating two simple tables: Table1 and Table2. Table2 has a foreign key that references Table1; the referential action is CASCADE for updates and NO ACTION for delete operations. NO ACTION is the default, so specifying it explicitly in the constraint definition is optional. The listing then attempts insertions into the two tables, including an insertion into Table2 that violates the foreign key relationship. SQL Server rejects the violation and returns the following error message:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_Table2_Table1_a'. The conflict occurred in
database 'pubs', table 'Table1', column 'a'.
The statement has been terminated.
The code then tries to delete a referenced row from Table1, fails, and returns the following error message:
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_Table2_Table1_a'. The conflict occurred in database 'pubs',
table 'Table2', column 'a'.
The statement has been terminated.
The code then successfully deletes from Table1 a row that Table2 doesn't reference. Finally, the code updates all primary key values in Table1, cascading the changes to Table 2.
Prev. page
[1]
2
3
next page 
