Primary Key and Foreign Key Constraints

Primary Key during table creation:


	CREATE TABLE PKExample (PKCOL INT IDENTITY PRIMARY KEY)
 

Primary Key after table has already been created:


	CREATE TABLE PKExample (PKCOL INT IDENTITY) GO ALTER TABLE PKExample ADD CONSTRAINT PK_CONSTRAINTNAME PRIMARY KEY (PKCOL)

 

Foreign Key during table creation:


	CREATE TABLE FKExample (FKCOL INT IDENTITY, PKCOL INT REFERENCES PKExample (PKCOL))

 

Foreign Key after table has already been created:


	CREATE TABLE FKExample (FKCOL INT IDENTITY, PKCOL INT) GO ALTER TABLE FKExample ADD CONSTRAINT FK_CONSTRAINTNAME FOREIGN KEY (PKCOL) REFERENCES PKExample (PKCOL)

 

Retrieve a list of all foreign key constraints in a database:


 Select FKEY.Name as ForeignKeyname,
OBJ.Name as ParentObject,
OBJ2.Name as ReferencedObject
 from Sys.Foreign_Keys FKEY
  Inner join Sys.Objects OBJ on FKEY.Parent_Object_ID = OBJ.Object_ID
  Inner join Sys.Objects OBJ2 on FKEY.Referenced_Object_ID = OBJ2.Object_ID

 

The parent object is that on which the foreign key is defined.
The referenced object is that to which the foreign key points.