How to Create FOREIGN KEY Constraint by Using WITH NOCHECK

When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint.
Database Engine will throw below error if any value is missing in the referenced column:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "NameOfConstraint". The conflict occurred in database "DBName", table "dbo.TableName", column 'ColumnName'.

However, by specifying WITH NOCHECK, the Database Engine can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column. The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.

ALTER TABLE TestFK WITH NOCHECK
ADD CONSTRAINT TestFK_IDFK FOREIGN KEY(IDFK)
REFERENCES TestPK (ID)

Note: You should be very careful while adding a constraint WITH NOCHECK option because this bypasses the controls in the Database Engine that enforce the data integrity of the table.

How to CREATE Foreign Key on existing table?

• We can create a FOREIGN KEY constraint as part of the table definition when we create a table.
• If a table already exists, we can add a FOREIGN KEY constraint, provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraints or UNIQUE constraint in any another or the same table.

Only 253 FOREIGN KEY constraint can be added to a table in SQL Server 2008. Click here to see the maximum sizes and numbers of various Database Objects defined in SQL Server 2008.

Creating FOREIGN Key on existing table
To explain this, I'll create a table TestFK without any Foreign Key. I will use Table TestPK to reference the Foreign Key. Below is T-SQL to create a TestFK table:

IF OBJECT_ID('TestFK','U') IS NOT NULL
DROP TABLE TestFK
GO
CREATE TABLE TestFK (
   [IDFK] [int] NULL,
   [Date] [datetime] DEFAULT (GETDATE())
)
GO
INSERT INTO TestFK (IDFK) VALUES
(1),(2),(1),(NULL),(2),(NULL),(3)
GO
SELECT [IDFK],[Date] FROM TestFK (NOLOCK)

Now ALTER the table to ADD Foreign Key
Execute below T-SQL code to add foreign key:

ALTER TABLE TestFK
ADD CONSTRAINT TestFK_IDFK FOREIGN KEY (IDFK)
REFERENCES TestPK (ID)

How to CREATE Primary Key on existing table?

Only single PRIMARY KEY constraint can be added to a table.

• Primary Key can be added when the table is created, as part of the table definition.
• Primary Key can be added to an existing table, provided that no other PRIMARY KEY constraint already exists.

How to CREATE Primary Key on existing table?

To explain this, I'll create a table without Primary Key constraint. Below is T-SQL to create a TestPK table:

IF OBJECT_ID('TestPK','U') IS NOT NULL
DROP TABLE TestPK
GO
CREATE TABLE TestPK (
    [ID] [int] NOT NULL,
    [Name] [varchar](50),
    [DoB] [date],
    [DeptNo] [tinyint]
)
GO
INSERT INTO TestPK (ID,Name,DoB,DeptNo) VALUES
(1,'Name1','1983-04-07',1),
(2,'Name2','1992-10-21',3),
(3,'Name3','1981-02-17',1),
(4,'Name4','1975-11-23',2)
GO
SELECT ID,Name,DoB,DeptNo FROM TestPK (NOLOCK)


Now ALTER the table to ADD Primary Key
Execute below T-SQL code to add primary key:

ALTER TABLE TestPK
ADD CONSTRAINT TestPK_PrimaryKey_ID PRIMARY KEY (ID)

Note: Before adding a PRIMARY KEY constraint to an existing column(s) in the table, ensure that the existing data follows the rules for primary keys:
1. No NULL values  2. No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.