SQL Server Foreign Key Update and Delete Rules

By:   |   Comments (12)   |   Related: 1 | 2 | 3 | 4 | 5 | > Constraints


Problem

Foreign key constraints are an integral part of SQL Server database design. These are used to maintain integrity among related data in different tables. While implementing update and delete operations on values in the parent table (referenced table with primary key) we have to consider the impact on related values in the child table. SQL Server provides different rules for managing the effect of updates and deletes on child table values. How can these rules be used effectively without threatening the relational integrity?

Solution

Foreign key constraints may be created by referencing a primary or unique key. Foreign key constraints ensure the relational integrity of data in associated tables. A foreign key value may be NULL and indicates a particular record has no parent record. But if a value exists, then it is bound to have an associated value in a parent table. When applying update or delete operations on parent tables there may be different requirements about the effect on associated values in child tables. There are four available options in SQL Server Server 2005 and later as follows:

  • No Action
  • Cascade
  • SET NULL
  • SET Default

It is not necessary that the same rule be applied for both update and delete operations. There may be different rules for each of the update and delete operations on a single FK constraint. Before proceeding with the demo, here is summary of the effects for update and delete operations:

Specification Update operation on parent table Delete operation on parent table
No Action Not allowed. Error message would be generated. Not allowed. Error message would be generated.
Cascade Associated values in child table would also be updated. Associated records in child table would also be deleted.
Set NULL Associated values in child table would be set to NULL. Foreign key column should allow NULL values to specify this rule. Associated values in child table would be set to NULL. Foreign key column should allow NULL values to specify this rule.
Set Default Associated values in child table would be set to default value specified in column definition. Also default value should be present in primary key column. Otherwise basic requirement of FK relation would fail and update operation would not be successful. If no default value is provided in foreign key column this rule could not be implemented. Associated values in child table would be set to default value specified in column definition. Also default value should be present in primary key column. Otherwise basic requirement of FK relation would fail and delete operation would not be successful. If no default value is provided in foreign key column this rule could not be implemented.

The following demonstrates each of these options.

Create and populate tables having FK relation

Now let's create a couple of tables to resemble a foreign key relationship.

-- Script 1: Create sample tables

-- Use required database
Use AdventureWorks
GO

-- Create child table
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'EmpEducation' AND [type] = 'U')
DROP TABLE EmpEducation
CREATE TABLE EmpEducation
(
EduID SMALLINT IDENTITY(1,1) PRIMARY KEY,
empno SMALLINT NULL DEFAULT 100,
DegreeTitle VARCHAR(50)
)
GO

-- Create parent table
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'employees' AND [type] = 'U')
DROP TABLE employees
CREATE TABLE employees
(
empno SMALLINT PRIMARY KEY ,
EmpName VARCHAR(70)
)
GO

The foreign key relation can be created either through SSMS GUI or T-SQL. Rules for update/delete operations may be specified explicitly. However if nothing is specified then the default rule is No Action. The rule may be changed to any other option at any time later by recreating the FK relation. Let's create the foreign key with the default specification through T-SQL.

-- Script 2: Create FK relationship

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
GO

Now populate the tables with sample data.

-- Script 3: Populate the tables with sample data

-- Insert records in parent table
INSERT INTO employees
SELECT 1, 'Atif' UNION ALL
SELECT 2, 'Shehzad' UNION ALL
SELECT 3, 'khurram' UNION ALL
SELECT 4, 'Ahmed' UNION ALL
SELECT 5, 'Uzair'
GO

-- Insert records in parent table
INSERT INTO EmpEducation
SELECT 1, 'MS' UNION ALL
SELECT 2, 'MBA' UNION ALL
SELECT 1, 'BS' UNION ALL
SELECT 2, 'MS' UNION ALL
SELECT 3, 'BS'
GO

Now we can look at the relationship along with the rules available in SSMS. In SSMS, right click on the FK for table EmpEducation and select modify to open the relationships frame as shown below.

foreign key relationships frame in ssms

NO ACTION option

For our data with default specification (No Action) SQL Server would not allow an update or delete operation on referenced values of the primary key table. Since we did not define a specification for our foreign key the default No Action is used. So based on this, no update or delete should be allowed for values that are referenced in the child table.

Let's verify the effect of the No Action rule.

-- Script 4: Update and delete with 'No Action' rule

-- Try to update referenced PK
UPDATE Employees
SET empno = 100 WHERE empno = 1
GO

-- Try to delete record with referenced PK
DELETE FROM Employees
WHERE empno = 2
GO

The following error messages are generated as result of the above script and the update and delete did not take place.

Msg 547, Level 16, State 0, Line 2
The UPDATE statement conflicted with the REFERENCE constraint "FK_EmpEducation_Employees". The conflict occurred in database "AdventureWorks", table "dbo.EmpEducation", column 'empno'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 3
The DELETE statement conflicted with the REFERENCE constraint "FK_EmpEducation_Employees". The conflict occurred in database "AdventureWorks", table "dbo.EmpEducation", column 'empno'.
The statement has been terminated.

CASCADE option

Now let's change the default specification (No Action) to Cascade. Select the CASCADE rule from the SSMS GUI as shown in the above screenshot or use this T-SQL code.

The following script adds "ON DELETE CASCADE ON UPDATE CASCADE"

-- Script 5: Create FK relationship with CASCADE

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
ON DELETE CASCADE ON UPDATE CASCADE
GO

Run script #4 again to verify the result produced with the cascade option.

select the cascade rule from ssms gui or use this t-sql code

SET NULL option

To utilize the SET NULL rule for update/delete operations the foreign key column should allow NULL values otherwise the SET NULL specification would fail by generating an error message.

  • Run script # 1 - to recreate the objects
  • Run the following script which adds "ON DELETE SET NULL ON UPDATE SET NULL"
-- Script 6: Create FK relationship with SET NULL

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
ON DELETE SET NULL ON UPDATE SET NULL
GO
  • Run script # 3 to create the test data
  • Run script # 4 to verify the functionality

The data in child table (EmpEducation) shows that as a result of the update and delete operations foreign key values are set to NULL as shown below.

utilize the set null rule

SET DEFAULT option

For utilizing the SET DEFAULT rule for update/delete operations default value should be there for foreign key column. Else SET DEFAULT specification would fail by generating error message. Our foreign key column has default value 100, so we may proceed with following steps

  • Run script # 1 - to recreate the objects
  • Run the following script which adds "ON DELETE SET DEFAULT ON UPDATE SET DEFAULT"
-- Script 7: Create FK relationship with SET DEFAULT

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT
GO
  • Run script # 3 to create the test data
  • Run script # 4 to verify the functionality

We have specified a default value of 100 for empno in the child table. Script 4 would change the corresponding four values to 100 as shown below.

set default option for foreign keys in sql 2005 and sql 2008

Drop the created objects

The following script drops the objects we created for this demo.

-- Script 8: Drop the created objects
-- Use required database
Use AdventureWorks
GO

-- Drop child table
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'EmpEducation' AND [type] = 'U')
DROP TABLE EmpEducation
GO

-- Drop parent table
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'employees' AND [type] = 'U')
DROP TABLE employees
GO
Next Steps
  • Analyze your tables and create proper foreign key relations where they are missing.
  • It's good to have full understanding of these rules, but keeping the default rule NO ACTION and using scripts to first operate on foreign key values and then primary keys is a safer option.
  • To avoid unexpected results comprehensive testing should be performed before using these rules for a given situation.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, August 29, 2019 - 11:21:07 AM - Greg Robidoux Back To Top (82183)

Thanks Michele, we made the update.

Thursday, August 29, 2019 - 9:41:48 AM - Michele Back To Top (82182)

Atif,

Thank you for this article!  It always helps to walk through something rather than read a description of what it does.

(I hope you don't mind a minor edit suggestion - for the Set Default section, step 3 is unecessary, since it is covered by step 2)

Monday, May 26, 2014 - 1:32:02 AM - Atif Shehzad Back To Top (31938)

For case 1:

It looks that value for this foreign key exist in child table. You can verify the existance of child records by following query on child table

select * from dbo.AcJournalDetail where AcJournalID = 783

 

For case 2:

If you want to delete related records from child as well as from parent table in one go then you can use CASCADE Option. I hope it will solve your issue.

Regards

Sunday, May 25, 2014 - 2:55:31 AM - Aftab Back To Top (31930)

Hi Sir,

 

I have 2 tables AcJournalMaster and  AcJournalDetail and associated with AcJournalID as Foreign in AcJournalDetail .

The default rules is as you mentioned "No Action" same is in my Case.

Case 1

What happen I deleted some records from the Child Table - AcJournalDetail and now how  can I delete the related records from the  Parent table - AcJournalMaster 

 

This Command gives Error as I already deleted the Related child record

Delete from AcJournalMaster where AcJournalMaster.AcJournalID=783

The DELETE statement conflicted with the REFERENCE constraint "FK_AcJournalDetail_AcJournalMaster". The conflict occurred in database "SAMERDB", table "dbo.AcJournalDetail", column 'AcJournalID'.

The statement has been terminated.

 

Case 2

 

How Can I delete records from both tables in one go.. Same error appear If I try to Delete records from Parent Table without deleting from child.

 

Delete from AcJournalMaster where AcJournalMaster.AcJournalID=783

The DELETE statement conflicted with the REFERENCE constraint "FK_AcJournalDetail_AcJournalMaster". The conflict occurred in database "SAMERDB", table "dbo.AcJournalDetail", column 'AcJournalID'.

The statement has been terminated.

As when I using 

 

Sunday, May 26, 2013 - 3:50:35 AM - Atif Back To Top (25142)

As you mentioned that the column barcode is involved in primary and foreign key relationship. So updating the price will have no effect any where else. The options mentioned in this tip are only applicable when you update or delete the primary key i.e. in your case it is barcode not the price.

I hope it is now clear or please communicate further if i did not follow your point.

Thanks

 

Friday, May 24, 2013 - 2:40:56 AM - Junaid Back To Top (25117)

Thank you Atif for your article. It has somewhat given me a new approuch in my development.

Could you advice me on the following:

I have made a POS software. Here products are being added with their barcode and price.

The barcode is the primary key in the table [products] and has a foreign key in another table [soldProductsInReceipt] also.

Now later if I want to change the price of a product and if I choose the relation to be CASCADE, all products in [soldProductsInReceipt]

will be changed aswell and give me wrong Reports in terms of revenue etc...

 

So I have put NO ACTION and whenever a price needs to be changed, a backup of all data needs to be made (for reporting reference) and start with a complete new database with the desired Prices.

 

Is this a good approach?

Thursday, January 31, 2013 - 2:52:39 AM - Atif Shehzad Back To Top (21822)

@Pavan. Please further elaborate your question. The mentioned rules of foreign keys are configured at table level.

Thanks

 

Tuesday, January 29, 2013 - 10:02:31 AM - pavan Back To Top (21770)

Hello:

I would like to know of how rules can be implemented at database level and how is it different from just writing the same in a stored procedure. Appreciate if you could please help me with a detailed example to understand the rule based mechanism.

 

Thanks

Pavan

Tuesday, May 3, 2011 - 10:26:02 AM - Vishal Gamji Back To Top (13736)

Nice article with good guidance on managing FKs.

Monday, April 25, 2011 - 8:52:40 PM - Jeremy Kadlec Back To Top (13682)

Gentlemen,

 

Let’s agree to disagree on terminology and focus on the fact that this tip outlines the four rules available in SQL Server to manage foreign key relationships for DELETE and UPDATE commands.

 

Thank you,

Jeremy Kadlec

Friday, April 22, 2011 - 1:27:11 AM - Atif Shehzad Back To Top (13666)

I have noted the term Parent/Child table at various articles in MSDN. For example here is a sentence from MSDN

"The example shown in this topic uses tables from the AdventureWorks database. The example uses the child Product table that contains the foreign-key column ProductCategory that is defined in the parent table ProductCategories"

So the real world rule is OK that you can not be your own parent, but in RDBMS the term "Parent/Child" is used. Please provide some refrence from BOL for your point.

 

Thanks

Thursday, April 21, 2011 - 1:22:20 PM - Joe Celko Back To Top (13663)

SQL and RDBMS do not have "Child" and "Parent" tables. That was the old CODASYL network model. We have "Referencing" and "Referenced" tables.

They are quite different; you cannot be your own parent, but you can make self-references. A child has s siginal parent and navigation is done in a record-at-a-time manner (did you read Bachman's original paper?). Referencing is a schema level concept.














get free sql tips
agree to terms