NO ACTION, CASCADE, SET NULL, and SET DEFAULT. In this short guide we will use one simple example to see exactly how each one behaves.
Quick Refresher: Foreign Keys
A foreign key is a column in one table (the child) that must match a primary key value in another table (the parent). It enforces referential integrity — every child row must point to a real parent row.
But what happens when someone deletes or updates the parent row? Do we block the change? Delete the children? Set them to NULL? That decision is called the cascading rule, and you attach it to the foreign key.
Our Example: Departments and Employees
Let's build a very simple two-table example we'll use throughout the article.
-- Parent table
CREATE TABLE Departments (
DeptId INT PRIMARY KEY,
DeptName VARCHAR(50)
);
INSERT INTO Departments VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'General'); -- used as our "default" dept
-- Child table
CREATE TABLE Employees (
EmpId INT PRIMARY KEY,
EmpName VARCHAR(50),
DeptId INT,
CONSTRAINT FK_Employees_Dept
FOREIGN KEY (DeptId) REFERENCES Departments(DeptId)
);
INSERT INTO Employees VALUES
(1, 'Anita', 1),
(2, 'Rohit', 1),
(3, 'Priya', 2);
Anita and Rohit work in Engineering. Priya works in Marketing. Now imagine someone tries to delete the Engineering department. What should happen to Anita and Rohit? That is exactly what cascading rules decide.
The Four Cascading Rules
NO ACTION
CASCADE
SET NULL
SET DEFAULT
You set these rules on the foreign key, using ON DELETE and ON UPDATE. You can set them to different values — for example, ON DELETE CASCADE with ON UPDATE NO ACTION.
Rule 1: NO ACTION (Default Behavior)
This is what you get when you don't specify anything. If someone tries to delete a parent row that still has children, SQL Server refuses and throws an error.
-- Using our default FK with no cascade option
DELETE FROM Departments WHERE DeptId = 1;
-- Error: The DELETE statement conflicted with the
-- REFERENCE constraint "FK_Employees_Dept". ...
This is the safest option — it protects your data by blocking any change that would orphan child rows. Most tables should stay with NO ACTION unless you have a specific reason to pick something else.
Rule 2: CASCADE
With CASCADE, deleting a parent also deletes all of its children. Updating the parent's key also updates the children's foreign key column. Think of it like cutting a tree trunk — all the branches fall too.
ALTER TABLE Employees
DROP CONSTRAINT FK_Employees_Dept;
ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Dept
FOREIGN KEY (DeptId) REFERENCES Departments(DeptId)
ON DELETE CASCADE
ON UPDATE CASCADE;
-- Now deleting Engineering also deletes Anita and Rohit
DELETE FROM Departments WHERE DeptId = 1;
Use CASCADE for tightly-owned data. Good example: an Orders table and its OrderItems — if the order is gone, the items should disappear too.
CASCADE can delete way more data than you expected, especially when multiple tables are chained. Always test on a backup or inside a transaction you can roll back.
Rule 3: SET NULL
With SET NULL, when the parent is deleted, the child's foreign key is set to NULL. The children are not deleted — they just become unassigned.
ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Dept
FOREIGN KEY (DeptId) REFERENCES Departments(DeptId)
ON DELETE SET NULL;
-- Delete Marketing
DELETE FROM Departments WHERE DeptId = 2;
-- Priya still exists, but her DeptId is now NULL
SELECT * FROM Employees;
Result:
| EmpId | EmpName | DeptId |
|---|---|---|
| 1 | Anita | 1 |
| 2 | Rohit | 1 |
| 3 | Priya | NULL |
NOT NULL, SQL Server won't let you use SET NULL.
Rule 4: SET DEFAULT
With SET DEFAULT, the child's foreign key is reset to that column's default value. This only works if the child column has a DEFAULT constraint, and that default value must exist as a real row in the parent table.
-- Step 1: Give DeptId a default of 3 (our "General" department)
ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_DeptId DEFAULT 3 FOR DeptId;
-- Step 2: Recreate the FK with ON DELETE SET DEFAULT
ALTER TABLE Employees
DROP CONSTRAINT FK_Employees_Dept;
ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Dept
FOREIGN KEY (DeptId) REFERENCES Departments(DeptId)
ON DELETE SET DEFAULT;
-- Now deleting Engineering reassigns its employees to DeptId 3
DELETE FROM Departments WHERE DeptId = 1;
SELECT * FROM Employees;
Result:
| EmpId | EmpName | DeptId |
|---|---|---|
| 1 | Anita | 3 |
| 2 | Rohit | 3 |
| 3 | Priya | 2 |
Perfect for cases where every child must have a parent. Instead of orphans or deletes, you simply reassign to a safe fallback row.
Comparison at a Glance
| Rule | On DELETE | Good For |
|---|---|---|
NO ACTION |
Blocks with error | Most tables — safest default |
CASCADE |
Children deleted automatically | Owned child data (e.g. OrderItems) |
SET NULL |
Child FK becomes NULL | Optional links (e.g. ManagerId on employees) |
SET DEFAULT |
Child FK reset to default value | Mandatory links with a fallback parent |
How to Pick the Right Rule
Ask yourself one question: "If the parent row disappears, what should logically happen to the child row?"
Should it be blocked? Use NO ACTION. Good for important lookup data like Countries, Categories, or Roles — you almost never want to remove these, and if you try, you should see an error.
Should the child be thrown away too? Use CASCADE. Good when the child cannot exist without the parent — an invoice line without its invoice, comments without their post.
Should the child live on without the link? Use SET NULL. Good for optional relationships — employees whose manager left, books whose author was removed.
Should the child fall back to a safe default? Use SET DEFAULT. Good for mandatory relationships where you have a catch-all parent like "General", "Unassigned", or "Misc".
Common Mistakes
Mistake 1: Overusing CASCADE. It looks convenient but can silently delete huge amounts of data when tables are chained.
Mistake 2: Using SET NULL on a NOT NULL column. SQL Server will refuse to create the constraint. Either allow NULLs or use SET DEFAULT instead.
Mistake 3: Using SET DEFAULT without a default value. The column must have a DEFAULT constraint, and the default value must exist in the parent table, or you'll hit a foreign key error.
Mistake 4: Creating multiple cascade paths to the same table. SQL Server blocks this with the error "may cause cycles or multiple cascade paths". The fix is to change one of the paths to NO ACTION and handle it in a trigger or in code.
Mistake 5: Forgetting about ON UPDATE. If you change a parent key (rare but possible), the child foreign key must follow. Always think about update behavior, not just delete.
The Interview Answer
"It is the rule you attach to a foreign key that tells SQL Server what to do with child rows when the parent is deleted or updated. There are four options —
NO ACTION, which is the default and blocks the change with an error; CASCADE, which deletes or updates the child rows automatically; SET NULL, which makes the child foreign key NULL; and SET DEFAULT, which resets the child foreign key to its default value."
Summary
Cascading referential integrity is how SQL Server decides what happens to child rows when their parent is deleted or updated. You get four choices — block the change (NO ACTION), propagate it (CASCADE), orphan the children (SET NULL), or fall back to a default parent (SET DEFAULT). You apply them to a foreign key using ON DELETE and ON UPDATE.
Most tables should stay with the default NO ACTION — it's the safest choice and protects against accidents. Pick CASCADE for tightly-owned data, SET NULL for optional relationships, and SET DEFAULT when every child needs a fallback. Think carefully before using CASCADE — it's powerful but can quietly erase much more than you intended.
| Concept | Key Takeaway |
|---|---|
| Referential integrity | Every child row must point to a real parent |
| Foreign key options | ON DELETE and ON UPDATE |
| NO ACTION | Default — blocks the change with error |
| CASCADE | Deletes / updates children automatically |
| SET NULL | Sets child FK to NULL |
| SET DEFAULT | Sets child FK to its default value |
| SET NULL requires | The child column to allow NULLs |
| SET DEFAULT requires | A DEFAULT constraint + default row in parent |
| Multiple cascade paths | Not allowed — break the cycle with NO ACTION |
| Interview line | "Four rules: NO ACTION, CASCADE, SET NULL, SET DEFAULT" |