Primary Key

A primary key is used to identify a record uniquely. A key is used to sort, compare and create a relationship between records. There are some basic rules to follow when creating a column for the primary key.

• A primary key can be just one column or a combination of columns.
• A table can only have one primary key.
• A primary key should never change or be reused. For example, a telephone number shouldn’t be used because someone could decide to get a new telephone number.
• A primary key cannot contain NULL values.
• The primary key cannot contain duplicate values.

Create a primary key on just one field:

 
CREATE TABLE Cars (
ID int NOT NULL PRIMARY KEY,
Color varchar(255)
);

Create a primary key on two fields also known as a composite primary key:

 
CREATE TABLE Cars (
  ID int NOT NULL,
  Model varchar(50) NOT NULL,
  Color varchar(255),
  CONSTRAINT PK_Cars PRIMARY KEY (ID,Model)
);

A common way to come up with a unique PRIMARY KEY is to use the IDENTITY to generate an ID:

CREATE TABLE Cars ( 
  ID int IDENTITY (1,1) NOT NULL,
  Model varchar(50) NOT NULL, 
  Color varchar(255),
  CONSTRAINT PK_Cars PRIMARY KEY (ID,Model)
);

If the table has already been created and we need to add a PRIMARY KEY then we can use the ALTER TABLE.

ALTER TABLE CARS
ADD PRIMARY KEY (ID)

Or if we need two fields to make up the PRIMARY KEY

ALTER TABLE Cars
ADD CONSTRAINT PK_Cars PRIMARY KEY (ID,Model);