Ranga Babu
Data check

SQL Foreign key

April 5, 2019 by

In this article let us review different ways to create a SQL foreign key, rules on updates and deletes, enabling foreign key constraints, disabling foreign key constraints and not for replication in foreign keys.

What is a foreign key?

A Foreign key is constraint that enforces referential integrity in SQL server database. It uses a column or combination of columns that is used establish link between the data in two tables to control the data that can be stored in the foreign key table.

Creating a Foreign key constraint

To create a SQL foreign key constraint, the parent table should have primary key column or column with UNIQUE constraint.

In this case, table Dept is parent table which has Primary key and will be referenced in child tables having foreign key.

Create a foreign key while creating a table

Below is the example of a T-SQL script to create a SQL foreign key while creating the table without defining the constraint name. If we do not specify the name of constraint, SQL server creates foreign key constraint with random name.


Below is the example of a T-SQL script to create a foreign key while creating the table with specific constraint name.


Create a foreign key after creating a table

Let us create the child table first and create a SQL foreign key constraint later. Below is example of creating foreign key constraint after creating table by specifying the constraint name.

Below is example of creating foreign key after creating table without specifying the constraint name.

Create a foreign key without checking for an existing data

In a few cases, the referencing table may already exist and have data which violates the SQL foreign key constraint you are going to create.

If we create a constraint with a check it will throw the below error as the data which already exist is violating the rule.

create foreign key error

If you still want to create a foreign key constraint by ignoring the existing data and validate the rule for further changes use “WITH NOCHECK”. The constraint you created is marked as not trusted.

Create a foreign key with DELETE/UPDATE rules

We can create a SQL foreign key constraint by specifying the what action to happen on referencing table when delete and update happens on primary key in parent table. Let us see some scenarios.

We can see the data exist in both parent and child table.

Data check

Let us create a foreign key with delete cascade and delete the primary key value in parent table.

After executing the above statement, we can see the that data is deleted in the child table as well.

Delete Cascade

Let us create a foreign key with update cascade and update the primary key in the parent table.

Data check


After executing the above statement, we can see DeptID is updated to 3 for records having DeptID 2 in the child table as well.

Update cascade

Similarly, we have the following actions.

  • SET NULL – Sets the SQL foreign key column value to null when the primary key value is either deleted or updated to a new value. If the column does not allow null values, the update/delete on primary key column fails and throw error

  • SET DEFAULT- Sets the default value on foreign key column when primary key value is updated or deleted. If the default constraint is not defined and column is nullable then foreign key column value is set to NULL. If the default constraint is not defined and column is not nullable then below error occurs and the change on primary key column is rolled back

    SQL foreign key - Validation error

  • NO ACTION – If the update or delete on primary key column fails the constraint rule then the change is rolled back

To modify the existing foreign key using T-SQL we must drop the foreign key constraint first and then re create it with new changes.

Disabling and enabling the foreign key constraint

Disable constraint

To disable a SQL foreign key constraint, we need to use below statement. Replace the table name and constraint name. When the foreign key constraint is disabled the constraint is marked as not trusted.

Enable constraint

To enable the constraint back use below statement.

Enable constraint with checking existing data

To force foreign key to check existing data while enabling the constraint use below statement. In this case if validation is success and constraint is enabled then the constraint is marked back to trusted.

Not for replication

When a foreign key is set “NOT FOR REPELCIATION”, the validation is done only when user inserts, deletes or updates data.

The validation is not fired when the replication agents sync the changes made by user to other end (i.e. subscriber in case of transactional and both subscriber and publisher in case of merge replication).

To create a SQL foreign key with “NOT FOR REPELCIATION”, use below script. By default, the constraint is marked as not trusted.

Even though we create the foreign key with “FOR REPLICATION” on the publisher, the snapshot agent scripts it as “NOT FOR REPLICATION” and foreign key is created as not for replication in subscriber when the snapshot is applied.

Indexing foreign keys columns

When a table is being referenced by a SQL foreign key, modifications on the primary key column in primary table will check the data in the child table. If the child table has a lot of data, it may slow down your change on the primary table. We may even encounter deadlocks in such cases.

Creating an index on a foreign key column helps in such cases.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu

Latest posts by Ranga Babu (see all)

6,935 Views