FOREIGN KEY CONSTRAINTS

Foreign key constraints identify a column or combination of columns whose values must exist

in another column or combination of columns in the same table or another table in the same database. Foreign key constraints manage referential integrity between tables or within a single table. To implement a foreign key constraint, you must follow these rules:

n The columns being referenced must have exactly the same data type (and collation, for string columns) as the local columns.

n The columns being referenced must have a unique index created on them. This is typically implemented using either a primary key or a unique constraint.

n Because the foreign key must reference a unique index, the foreign key columns have the same size limitations as that of the primary key and unique constraints.

You can also create foreign key constraints on computed columns. You can find information about which foreign key constraints exist in your database by querying the

sys.foreign_keys and sys.foreign_key_columns catalog views.

Foreign keys are usually queried frequently in user queries and in joins, as well as when SQL Server needs to verify referential integrity when deleting or updating primary key rows. This means that foreign keys usually greatly benefit from being indexed. Indexing is covered in greater detail in Chapter 6.

When a foreign key constraint notices a referential integrity violation because of a DELETE or an UPDATE of a row that it references, the default reaction is to raise an error message and roll back the statement that violated the constraint. If this is not the result you want, you can change the default action for the foreign key to delete the referenced row, update the referenced column, or both. There are four actions to choose from:

n NO ACTION (the default) n SET NULL

n SET DEFAULT n CASCADE

An example implementation is shown here:

CREATE TABLE Test.Customers (

CustomerID INT PRIMARY KEY );

CREATE TABLE Test.Orders (

OrderID INT PRIMARY KEY ,CustomerID INT NULL

REFERENCES Test.Customers ON DELETE SET NULL ON UPDATE CASCADE );

The default behavior of the foreign key is NO ACTION. If the foreign key finds a violation and NO ACTION is specified, SQL Server rolls back the statement that violated the constraint and raises an error message.

SET NULL and SET DEFAULT cause all the referenced values to be set to either NULL

(for SET NULL) or DEFAULT (for SET DEFAULT; that is, the default defined on the column) instead of raising an error and rolling back the statement. In the relationship between the Orders and Customers tables shown in the code sample, if a customer is deleted, the CustomerID column is set to NULL for all orders belonging to that customer and no error message is sent to the calling application.

The CASCADE action causes SQL Server to delete referenced rows for a DELETE statement (ON DELETE) and update the referenced values (ON UPDATE) for an UPDATE statement. Using the same code sample, if the CustomerID column is changed for a row in the Customers table, all corresponding rows in the Orders table are updated with the same CustomerID to reflect the change. If ON DELETE CASCADE is specified for the foreign key constraint and a row in the Customers table is deleted, all referencing rows in the Orders table are deleted. This might sound reasonable, but it might not be possible to implement CASCADE for all foreign key constraints because cyclic references are not supported. For example, in the following script, an error is raised when you try to add the foreign key FKCustomersLastOrder because it introduces a cyclic reference. If a customer is deleted, all referencing orders must be deleted, and all customers referencing those orders through the LastOrderID column must also be deleted:

CREATE TABLE Test.Customers ( CustomerID INT PRIMARY KEY ,LastOrderID INT NULL );

CREATE TABLE Test.Orders ( OrderID INT PRIMARY KEY ,CustomerID INT NOT NULL REFERENCES Test.Customers ON DELETE CASCADE ON UPDATE NO ACTION );

ALTER TABLE Test.Customers ADD CONSTRAINT FKCustomersLastOrder FOREIGN KEY (LastOrderID)

REFERENCES Test.Orders (OrderID) ON DELETE CASCADE

In the previous example, consider what happens if a customer is deleted—all the customer’s orders are also deleted. This might be fine, but consider the following code:

CREATE TABLE Test.Countries ( CountryID INT PRIMARY KEY );

CREATE TABLE Test.Cities ( CityID INT PRIMARY KEY ,CountryID INT NOT NULL REFERENCES Test.Countries ON DELETE CASCADE );

CREATE TABLE Test.Customers ( CustomerID INT PRIMARY KEY ,CityID INT NOT NULL REFERENCES Test.Cities ON DELETE CASCADE );

CREATE TABLE Test.Orders ( OrderID INT PRIMARY KEY ,CustomerID INT NOT NULL REFERENCES Test.Customers ON DELETE CASCADE );

In this example, if you delete a country, all cities in that country, all customers

in those cities, and all orders belonging to those customers are also deleted. Be cautious— you might be deleting more than you think. Consider someone executing the query

DELETE Test.Countries WHERE CountryID = 1; from SSMS. The person might think he is

deleting only one row in the Countries table, when he or she might actually be deleting millions of rows. The time it takes to execute this DELETE statement depends on how many rows are being deleted. When it finishes, SSMS returns the following message:

(1 row(s) affected)

This message is returned even if millions of rows were deleted because the message tells us only how many rows were deleted directly by the executed statement. There is nothing wrong with this behavior, but it is definitely something you should consider.

note tRiGGeRS

If you have defined foreign keys with cascading actions, any AFTER triggers on the affected tables are still executed, but they are executed after the whole chain of cascading actions have completed. If an error occurs while the cascading action chain is being executed, the entire chain is rolled back and no AFTER triggers are executed for that chain.