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.

What's the difference between a primary key and a unique key constraints?

Both Primary Key and Unique Key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.

Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL.

Primary key:

1. Primary key used to uniqly identify each row in Table.
2. Primary key does not allow duplicate values and Null values.
3. Primary key is default Clustered Indexes
4. One table can have only one Primary key.

 Unique Key:

1. Unique Key is used to uniqly identify each row in Table.
2. Unique Key does not allow duplicate values but allowes only one Null value.
3. Unique Key is default Non- Clustered Indexe
4. One table can have more than one Unique Keys.

Explain Candidate Key, Alternate Key, and Composite Key

A Candidate Key is one that can uniquely identify each row of a table.
Generally a candidate key becomes the Primary Key of the table.

If the table has more than one Candidate Key, one of them will become the Primary Key, and the rest are called Alternate Keys.

A key formed by combining at least two or more columns is called Composite Key.