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

is dropped, you go ahead and run the script in production. The problem is
that if you create the table using the script shown here, the PK will have a
different name on each server and your script will fail.
How do you name the key when you create it? What you name your
keys is mostly up to you, but we provide some naming guidelines in
Chapter 7. In this case we use pk_product_sku as the name of our PK. As
a best practice, we suggest that you always explicitly name all your primary
keys in this manner. In the following script we removed the PRIMARY KEY
statement from the sku column definition and added a CONSTRAINT state-
ment at the end of the table definition.
CREATE TABLE Products(
sku int NOT NULL,
modelnumber varchar(25) NOT NULL,
name varchar(100) NOT NULL,
manufacturer varchar(25) NOT NULL,
description varchar(255) NOT NULL,
price money NOT NULL,
weight decimal(5, 2) NOT NULL,
shippingweight decimal(5, 2) NOT NULL,
height decimal(4, 2) NOT NULL,
width decimal(4, 2) NOT NULL,
depth decimal(4, 2) NOT NULL,
isserialized bit NOT NULL,
status tinyint NOT NULL,
CONSTRAINT pk_product_sku PRIMARY KEY (sku)
)
Last, but certainly not least, what if the table already exists and you
want to add a primary key? First, you must make sure that any data already
in the column conforms to the rules of a primary key. It cannot contain
NULLs, and each row must be unique. After that, another simple script
will do the trick.
ALTER TABLE Products
ADD CONSTRAINT pk_product_sku PRIMARY KEY (sku)
But wait—there's more. Using the sku column as we have done here is
fine, but there are other PK options we need to discuss. If you were to go
through your entire database and define PKs as we have done on the
Products table, you would likely end up with a different column name in