Foreign Key Constraints

Foreign Key Constraints help us to create a relationship between tables in a SQL Server database. Unlike Primary key constraint we could have multiple foreign key constraints on one table. Both the primary key and foreign key columns in the referenced and the referencing table should have same data type.
To create a foreign key constraint on new table we could use as

–Referenced table
CREATE TABLE table1
(
col1 INT,
col2 VARCHAR(20),
CONSTRAINT pk_table1_col1 PRIMARY KEY (col1)
);
go

–Referencing table
CREATE TABLE table2
(
column1 INT,
column2 VARCHAR(20),
col1 INT,
CONSTRAINT pk_table2_column1 PRIMARY KEY(column1),
CONSTRAINT fk_table1_col1 FOREIGN KEY (col1) REFERENCES table1 (col1)
);
go

If we already created our table and later if we want to add the foreign key constraint we could do so as

ALTER TABLE table2
ADD CONSTRAINT fk_table1_col1 FOREIGN KEY (col1) REFERENCES table1 (col1);

To make sure that there are no values already exists in the table while creating the table we could use WITH CHECK statement as

ALTER TABLE table2
WITH CHECK ADD CONSTRAINT fk_table1_col1 FOREIGN KEY (col1) REFERENCES table1
(col1);

We could specify different cascade referential integrity options or what will happen to our foreign key column when the primary key column gets modified.
These options are SET NULL (change the foreign key column value to NULL), SET DEFAULT (change the foreign key column value to default value if there is already one or NULL), CASCADE (modify update/delete or foreign key column same as our primary key column), NO ACTION (do nothing).
For example in the previous foreign key if we want to enforce SET NULL cascade for delete and SET CASCADE for update on table1 we could do that as

ALTER TABLE dbo.table2
WITH CHECK ADD CONSTRAINT fk_table1_col1 FOREIGN KEY(col1) REFERENCES
dbo.table1 (col1)
ON UPDATE CASCADE
ON DELETE SET NULL;
go

ALTER TABLE dbo.table2
CHECK CONSTRAINT fk_table1_col1;
go

To check our foreign key is really created we could check it as

–check if foreign key constraint exists
SELECT *
FROM sys.foreign_keys
WHERE name = ‘fk_table1_col1’;

If we no longer need the foreign key constraint we could drop it as

–drop constraint
ALTER TABLE table2
DROP CONSTRAINT fk_table1_col1;

In some cases such as data warehouse house initial load we may need to disable foreign Key constraint in all tables so that our ETL load much faster and re-enable the constraint back.

To disable Foreign Key constraint on all tables you may use

EXECUTE Sp_msforeachtable
‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’;
go

To enable Foreign Key constraint back you may use

EXECUTE Sp_msforeachtable
‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL’
go