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

is for this reason that we strongly advise against using composite primary
keys; not only does it mean a lot of data duplication, but also it adds over-
head when you join tables. Going back to our employee and vehicle exam-
ple, take a look at Figure 3.2, which shows the tables with some sample
data.
F IGURE 3.2 Data from the employee and vehicle tables showing the
relationship between the tables
As you can see, both tables have objid columns. These are identity
columns and serve as our primary key. Additionally, notice that the vehicle
table has an employee_objid column. This column holds the objid of the
employee to whom the car is assigned. In SQL Server, the foreign key is
set up on the vehicle table, and its job is to ensure that the value you enter
in the employee_objid column is in fact a valid value that has a correspon-
ding record in the employee table.
The following script creates the vehicle table. You will notice a few
things that are different from the earlier table creation script. First, when
we set up the objid column, we use the IDENTITY(1,1) statement to cre-
ate an identity, with a seed and increment of 1 on the column. Second, we
have a second CONSTRAINT statement to add the foreign key relationship.
When creating a foreign key, you specify the column or columns in the ref-
erencing table that contain the foreign key as well as the referenced table
and columns that contain the primary key.