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.
|
1 2 3 4 |
CREATE TABLE Dept ( DeptID INT CONSTRAINT PK_Dept PRIMARY KEY ,DeptName VARCHAR(10) ) |
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.
|
1 2 3 4 5 6 7 |
CREATE TABLE Emp ( EmpID INT ,EmpFname VARCHAR(50) ,EmpLname VARCHAR(50) ,DeptID INT FOREIGN KEY REFERENCES Dept(DeptID) ,IsActive BIT ) |
Below is the example of a T-SQL script to create a foreign key while creating the table with specific constraint name.
|
1 2 3 4 5 6 7 |
CREATE TABLE Emp ( EmpID INT ,EmpFname VARCHAR(50) ,EmpLname VARCHAR(50) ,DeptID INT CONSTRAINT FK_Emp FOREIGN KEY REFERENCES Dept(DeptID) ,IsActive BIT ) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE Emp ( EmpID INT ,EmpFname VARCHAR(50) ,EmpLname VARCHAR(50) ,DeptID INT ,IsActive BIT ) ALTER TABLE [dbo].[Emp] WITH CHECK ADD CONSTRAINT [FK_Emp] FOREIGN KEY([DeptID]) REFERENCES [dbo].[Dept] ([DeptID]) GO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp] GO |
Below is example of creating foreign key after creating table without specifying the constraint name.
|
1 2 3 4 5 6 |
ALTER TABLE [dbo].[Emp] WITH CHECK ADD FOREIGN KEY([DeptID]) REFERENCES [dbo].[Dept] ([DeptID]) GO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp] GO |
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.
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.
|
1 2 3 4 5 6 |
ALTER TABLE [dbo].[Emp] WITH NOCHECK ADD CONSTRAINT [FK_Emp] FOREIGN KEY([DeptID]) REFERENCES [dbo].[Dept] ([DeptID]) GO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp] GO |
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.
Let us create a foreign key with delete cascade and delete the primary key value in parent table.
|
1 2 3 4 5 6 7 8 9 |
ALTER TABLE [dbo].[Emp] WITH CHECK ADD CONSTRAINT [FK_Emp2] FOREIGN KEY([DeptID]) REFERENCES [dbo].[Dept] ([DeptID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp2] GO delete from Dept where DeptID =1 |
After executing the above statement, we can see the that data is deleted in the child table as well.
Let us create a foreign key with update cascade and update the primary key in the parent table.
|
1 2 3 4 5 6 7 8 9 |
ALTER TABLE [dbo].[Emp] WITH CHECK ADD CONSTRAINT [FK_Emp3] FOREIGN KEY([DeptID]) REFERENCES [dbo].[Dept] ([DeptID]) ON UPDATE CASCADE GO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp3] GO update Dept set DeptID =3 where DeptID=2 |
After executing the above statement, we can see DeptID is updated to 3 for records having DeptID 2 in the child table as well.
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
- 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.
|
1 |
ALTER TABLE [Emp] NOCHECK CONSTRAINT [FK_Emp2] |
Enable constraint
To enable the constraint back use below statement.
|
1 |
ALTER TABLE [Emp] CHECK CONSTRAINT [FK_Emp2] |
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.
|
1 2 3 |
ALTER TABLE [Emp] WITH CHECK CHECK CONSTRAINT [FK_Emp2] GO |
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.
|
1 2 3 4 |
ALTER TABLE [dbo].[Emp] WITH CHECK ADD CONSTRAINT [FK_Emp2] FOREIGN KEY([DeptID]) REFERENCES [dbo].[Dept] ([DeptID]) NOT FOR REPLICATION GO |
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.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019