Another foreign key consideration is table ownership. I recommend having one owner, preferably the database owner (DBO), own all tables in a database, but in some cases this restriction might not be preferable. The owner of a table can't declare a foreign key reference to another table unless the owner of the other table has granted REFERENCES permission to the first table owner. The owner of the first table must have REFERENCES permission even if that owner already has permission to select from the table to be referenced. This restriction prevents another user from accessing your tables without your knowledge or consent and changing the way operations are performed. (If someone is able to create a foreign key that references one of your tables, you might be disallowed from modifying your own table because the change would violate the relationship that someone else set up.) You can grant any user REFERENCES permission even if you don't also grant SELECT permission, and vice versa. The only exception is that the DBO, or any user who is a member of the db_owner role, has full default permissions on all objects in the database.
When using foreign key constraints, also consider performance and indexing. In deciding how to use foreign key relationships, you need to balance the protection they provide against the corresponding performance overhead. Be careful not to add constraints that form logically redundant relationships. Excessive use of foreign key constraints can severely degrade the performance of seemingly simple operations.
The columns you specify in foreign key constraints are often strong candidates for index creation. If you build the index with the same key order you used in the primary key or the Unique constraint of the table that the foreign key references, SQL Server can perform joins efficiently. Also, a foreign key might be a subset of the table's primary key. In the Northwind database's Order Details table, OrderId is part of the primary key and is also a foreign key. Because OrderId is part of a primary key, it's already part of an index. In the Order Details table, OrderId is the index's lead column, so building a separate index on that column alone probably isn't warranted. However, if OrderId weren't the lead column of the index, building an index on it might make sense.
Enforcing Foreign Key Constraints
SQL Server offers three options for controlling enforcement of foreign key constraints. You can add constraints after adding data, temporarily disable checking of foreign key references, or use the bulk copy program (bcp) or BULK INSERT command to initially load data and avoid checking foreign key constraints.
First, if you want to add constraints after adding data, don't define your constraints in the CREATE TABLE statement. After loading the initial data, you can add constraints by using the ALTER TABLE command. When you use ALTER TABLE to add a new foreign key constraint for a table that already has data, SQL Server by default checks the existing data to verify the constraints. If constraint violations exist, the database won't add the constraint. With large tables, such a check can take a long time.
But you do have an alternative—you can add a foreign key constraint and omit the check by specifying the WITH NOCHECK option in an ALTER TABLE statement. SQL Server will check all subsequent operations but won't check existing data. This process is sometimes called deferring a constraint. Here's an example that adds a foreign key constraint to an existing table (notice the clause WITH NOCHECK).
ALTER TABLE table2
WITH NOCHECK
ADD CONSTRAINT FK_Table2_Table1_a FOREIGN KEY(a)
REFERENCES Table1(a)
With the second option, the table owner can temporarily disable checking of foreign key references by using the
ALTER TABLE table_name NOCHECK CONSTRAINT
constraint_name
statement. You can reestablish the FOREIGN KEY constraint by using
ALTER TABLE table_name CHECK CONSTRAINT constraint_name
Note that when you use this method to reenable an existing constraint, SQL Server doesn't automatically check to see that all rows still satisfy the constraint. To check the existing rows, you can simply issue a dummy update by setting a column to itself for all rows (such as UPDATE ORDERS SET cust_id = cust_id), and if any constraint violations occur, you can fix them.
The two options above are similar in their syntax and can be confusing. The main difference between the two is whether you use the word WITH. To defer a constraint, you ALTER the table WITH NOCHECK. To disable a constraint, you ALTER the TABLE and specify NOCHECK followed by the name of the constraint.
Finally, you can use bcp or the BULK INSERT command to initially load data. The BULK INSERT command and the bcp program by default don't check any foreign key constraints. You can use the CHECK_CONSTRAINTS option to override this behavior. BULK INSERT and bcp are faster than regular INSERT commands because they generally bypass the usual integrity checks and most logging. If you choose to execute bcp or BULK INSERT without checking constraints, you could later perform a dummy update to flag any violations in the existing data, as I described in the case of reenabling a constraint. Otherwise, your users might see constraint error messages when they perform update operations on preexisting data, even if they haven't changed any values.
The ability to specify a CASCADE referential action was one of the most common feature requests that the SQL Server 2000 development team received. Other database systems, including Microsoft Access, support this capability. However, carefully scrutinize your application design before adding CASCADE actions to your tables, or even before defining foreign key constraints. You might find that using application logic to enforce referential actions other than NO ACTION is more often appropriate. Although referential actions might be intuitive, ask yourself how many applications could apply this feature? How many real-world examples involve an application that is so simplistic that you would unconditionally delete (or set to the default or to NULL) all matching rows in a related table? Most applications perform some additional processing, such as asking users whether they really want to delete a customer who has open orders. The declarative nature of referential actions doesn't provide a way to hook in application logic to handle cases like these. Probably the most practical course of action is to use SQL Server's foreign key constraints to guarantee that nothing violates relationships and have each application deal with CASCADEs and other special referential actions individually.
End of Article
Prev. page
1
2
[3]
next page -->
