PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 65

CREATE TABLE dbo.vehicle(
objid int IDENTITY(1,1) NOT NULL,
make varchar(50) NOT NULL,
model varchar(50)NOT NULL,
year char(4) NOT NULL,
employee_objid int NOT NULL,
CONSTRAINT PK_vehicle PRIMARY KEY (objid),
CONSTRAINT FK_vehicle_employee
FOREIGN KEY(employee_objid)
REFERENCES employee (objid)
)
Once your primary keys are in place, the creation of the foreign keys is
academic. You simply create the appropriate columns on the referencing
table and add the foreign key. As stated in Chapter 2, if your design re-
quires it, the same column in a table can be in both the primary key and a
foreign key.
When you create foreign keys, you can also specify what to do if an up-
date or delete is issued on the parent table. By default, if you attempt to
delete a record in the parent table, the delete will fail because it would re-
sult in orphaned rows in the referencing table. An orphaned row is a row
that exists in a child table that has no corresponding parent. This can cause
problems in some data models. In our employee and vehicle tables, a
NULL in the vehicle table means that the vehicle has not been assigned to
an employee. However, consider a table that stores orders and order de-
tails; in this case, an orphaned record in the order detail table would be
useless. You would have no idea which order the detail line belonged to.
Instead of allowing a delete to fail, you have options. First, you can
have the delete operation cascade, meaning that SQL Server will delete
all the child rows along with the parent row you are deleting. Be very care-
ful when using this option. If you have several levels of relationships with
cascading delete enabled, you could wipe out a large chunk of data by is-
suing a delete on a single record.
Your second option is to have SQL Server set the foreign key column
to NULL in the referencing table. This option creates orphaned records,
as discussed. Third, you can have SQL Server set the foreign key column
back to the default value of the column, if it has one. Similar options are
also available if you try to update the primary key value itself. Again, SQL
Server can either (1) cascade the update so that the child rows still point to
the correct parent rows with the new key, (2) set the foreign key to NULL,
or (3) set the foreign key back to its default value.