You can also define foreign keys through the SQL Server Enterprise Manager. In the left pane, open the database, then select Tables. When the right pane lists all the tables, right-click the referencing table, and select Design Table. Select the Table and Index Properties button on the toolbar, and select the Relationships tab. Figure 1 shows the definition of a foreign key from Table2 to Table1. The dialog box lets you name the constraint and choose the primary key (referenced) table and the foreign key (referencing) table. Drop-down lists let you choose which column from each table will make up the key. At the bottom of the dialog box, you can choose to maintain the relationship between the tables by applying a CASCADE operation for updates to the primary key, deletes to the primary key, or both. When a box is clear, the default behavior of NO ACTION (disallowing the change) is in effect.
Triggers
As I mentioned, in releases earlier than SQL Server 6.0, triggers are the only way to perform referential actions. In SQL Server 7.0 and 6.5, you need to use triggers to perform any action other than the default NO ACTION. In SQL Server 2000, triggers still have some valuable functions for enforcing referential integrity.
Even with support for the CASCADE action, SQL Server 2000 can perform only two of the four ANSI-defined referential actions. To delete rows from a referenced table and have the referencing table replace the foreign key with a default value or NULL, you still must use triggers. Listing 2 shows a simple trigger that sets all referencing values in Table2 to NULL in the case of a delete from Table1.
You can also use triggers to make error messages friendlier. The preceding section showed the error messages that SQL Server generates automatically when you violate a foreign key constraint; they aren't pretty. If you write triggers to check referential integrity, you can produce any error message you want.
If you use triggers to enforce referential integrity, keep a couple of things in mind. First, you can't combine foreign key constraints and triggers for the same primary key/foreign key relationship. Referential integrity applies to modifications to both tables. Second, you can't use a foreign key constraint to ensure that new rows inserted into the referencing table are valid and also expect a trigger to cascade any updates or deletes to the referenced table. SQL Server lets you create both the foreign key constraint and the trigger. But if you try to delete a referenced row in the referenced table, SQL Server will check the constraint first, determine that deleting the row would violate the referential integrity, and disallow the delete. Thus, any delete trigger on the referenced table will never fire. If you decide to enforce referential integrity with triggers, you need to write two triggers: one on the referenced table to handle deletes and updates, and one on the referencing table to handle inserts and updates. You still might want to declare the foreign key relationship to clarify the relationship between the tables. Just use ALTER TABLE's NOCHECK option to ensure that SQL Server won't enforce the constraint; then the trigger will fire.
A drawback of using triggers to enforce referential integrity is that you can have problems handling updates to multiple rows in the referenced table. For example, suppose that you want all primary keys in Table1 to be multiples of 10 instead of single-digit integers. Issuing an update to Table1 to multiply all primary key values by 10 would be simple, but writing a trigger to propagate that change to all the foreign keys would be a difficult, or even impossible, task. SQL Server uses a pseudo-table called inserted to keep track of all new rows that an update statement generates. All old versions of the changed rows are in the pseudo-table deleted. These pseudo-tables are accessible only inside a trigger.
To write a trigger to propagate the changes, you need to know which rows in inserted correspond to which rows in deleted. An update trigger to propagate the updated values in Table1 would need to update Table2. And for each row in Table2 that has a value matching a value in deleted, you'd have to change the value in Table2 to the corresponding value in inserted. Deleted might have the values 1, 2, and 3, and inserted might have the values 10, 20, and 30.
Programmatically, you can't associate the rows in the two pseudo-tables with each other. Some people try to use cursors to step through the rows in both tables, assuming that the first row in inserted corresponds to the first row in deleted. However, relational database management systems (RDBMSs) such as SQL Server have no concept of a first row; to them, a table is an unordered set of rows. You can't use triggers to propagate multiple updates of a primary key from a referenced table to a referencing table, unless another column in the referenced table is also unique. You could then use this unique column to join the inserted and deleted tables to determine which old values correspond to which new values.
Don't let this limitation deter you from considering triggers if SQL Server's foreign key capabilities don't meet your needs. Only in rare situations will you need to update multiple primary keys in one update statement, and sometimes you might want to disallow all updates to primary keys. If you need to update multiple primary keys, you could write a special script for that purpose. You could use the ALTER TABLE command to temporarily disable your triggers, then write the code that updates both the referenced table and the referencing table to the new values.
SQL Server 2000 introduces a new trigger capability that also relates to foreign keys. Triggers in SQL Server 7.0 and earlier releases are after triggers—that is, they execute after the data-modification statement takes effect. If you have a delete trigger on Table1, for example, the deleted rows aren't part of Table1 while the trigger is active; they've already been deleted. Other RDBMSs have before triggers, which specify actions for the database system to perform before modifying the data. SQL Server 2000 has a similar feature, called instead-of triggers, which differ from before triggers in that they replace the data-modification operation. If you have an instead-of trigger for a delete on Table1 and you try to execute a delete statement on Table1, the instead-of trigger will fire. The delete won't happen unless the instead-of trigger reissues the delete statement.
I won't go into all the details of instead-of triggers, but they're intended primarily for modifying views that you can't otherwise change, such as views spanning multiple tables. Usually, you can't delete from a view that is based on a join. However, an instead-of trigger on the view lets you execute separate deletes on each table the view is based on.
Be aware that you can't combine instead-of triggers and foreign keys with the CASCADE referential action. If you define any foreign keys with CASCADE on a table, you get the following error message if you try to create an instead-of trigger for the same action.
<I>Server: Msg 2113, Level 16, State 1, Procedure Table2_IOT_update, Line 5</I>
<I>Cannot CREATE INSTEAD OF DELETE or UPDATE TRIGGER 'Table2_IOT_update'
on table 'Table2' because the table has a foreign key with cascaded DELETE
or UPDATE.</I>
This restriction means that if the CASCADE action is for updates, you can't also have an instead-of trigger for updates, but you can have instead-of triggers for inserts or deletes. Similarly, if you have an instead-of trigger on Table2, you can't alter the table to add a foreign key constraint with the CASCADE action for the same data-modification operation.
Working with Foreign Key Constraints
Here are a few additional details about foreign key constraints. In general, a foreign key in one table references a primary key in another table. You can use a foreign key constraint to reference a column with a Unique constraint (instead of one with a primary key constraint). However, referencing a primary key is more typical and is generally better practice.
Using identical column names in tables involved in a foreign key reference isn't necessary, but it's often good practice. Consider a Customers table that an Orders table references. The cust_id and location_num column names are defined in the Customers table. The Orders table could use the column names cust_num and cust_loc, but the different column names might make the tables more cumbersome to work with.
Although the names of related columns can differ, the data types of related columns must be identical except for nullability and variable-length attributes. For example, a column of type char(10) NOT NULL can reference one of type varchar(10) NULL, but it can't reference a column of type char(12) NOT NULL because the length definition is different in the two columns. Likewise, a column of type smallint can't reference a column of type int.
Prev. page
1
[2]
3
next page 
