Tutorial by Examples

-- Identity primary key - unique arbitrary increment number create table person ( id int identity(1,1) primary key not null, firstName varchar(100) not null, lastName varchar(100) not null, dob DateTime not null, ssn varchar(9) not null )

-- GUID primary key - arbitrary unique value for table create table person ( id uniqueIdentifier default (newId()) primary key, firstName varchar(100) not null, lastName varchar(100) not null, dob DateTime not null, ssn varchar(9) not null )

-- natural primary key - using an existing piece of data within the table that uniquely identifies the record create table person ( firstName varchar(100) not null, lastName varchar(100) not null, dob DateTime not null, ssn varchar(9) primary key not null )

-- composite key - using two or more existing columns within a table to create a primary key create table person ( firstName varchar(100) not null, lastName varchar(100) not null, dob DateTime not null, ssn varchar(9) not null, primary key (firstName, lastName, dob) )

ALTER TABLE person ADD CONSTRAINT pk_PersonSSN PRIMARY KEY (ssn) Note, if the primary key column (in this case ssn) has more than one row with the same candidate key, the above statement will fail, as primary key values must be unique.

ALTER TABLE Person DROP CONSTRAINT pk_PersonSSN