In SQL Server, ON DELETE NO ACTION is an option that we can apply to foreign key constraints to prevent the deletion of a row in the parent table if there are related rows in the child table.

Unlike ON DELETE CASCADE, which would delete the related rows in the child table, NO ACTION enforces that if a deletion would result in orphaned records in the child table, the deletion operation is prohibited, and an error is raised.

Actually, ON DELETE NO ACTION is the default option for foreign keys in SQL Server, so if we don’t specify the ON DELETE option at all, then this is what is used.

Example Scenario

Suppose we have two tables: Authors and Books. Each book is associated with an author, and we want to enforce that an author cannot be deleted if they have books in the Books table.

The schema looks something like this:

SQL Scripts

Below are the SQL scripts we can use to create the above tables and their relationship, as well as how we might go about trying to delete a row in the parent (Customers) table.

Creating the Customers and Orders Tables

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    AuthorName NVARCHAR(100) NOT NULL
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
    ON DELETE NO ACTION
);

Inserting Data

-- Insert some authors
INSERT INTO Authors (AuthorID, AuthorName) VALUES (1, 'George R.R. Martin');
INSERT INTO Authors (AuthorID, AuthorName) VALUES (2, 'J.K. Rowling');
INSERT INTO Authors (AuthorID, AuthorName) VALUES (3, 'Isaac Asimov');

-- Insert some books by these authors
INSERT INTO Books (BookID, Title, AuthorID) VALUES (101, 'A Game of Thrones', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (102, 'A Clash of Kings', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (103, 'Harry Potter and the Sorcerer''s Stone', 2);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (104, 'Foundation', 3);

Testing ON DELETE NO ACTION

Now to the moment of truth. Will the delete operation work or not?

Let’s find out…

-- Attempt to delete an author with existing books
DELETE FROM Authors WHERE AuthorID = 1;

Result:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Books__AuthorID__1BC821DD". The conflict occurred in database "demo", table "dbo.Books", column 'AuthorID'.

We got an error, as expected. This error occurred because the ON DELETE option was set to NO ACTION. As mentioned, this is the default option, and so we would have received the same error even if we hadn’t specified an ON DELETE option when defining the foreign key.

When this option is being used, we need to delete all child rows before attempting to delete the parent row.

So to successfully delete an author, we must first delete all related books first:

-- First delete books associated with AuthorID = 1
DELETE FROM Books WHERE AuthorID = 1;

Output:

(2 rows affected)

And now we can delete the author:

DELETE FROM Authors WHERE AuthorID = 1;

Output:

(1 row affected)

Yay… success!

So to recap, the ON DELETE NO ACTION option in this example ensures that an author cannot be deleted from the Authors table if they have books listed in the Books table. This maintains referential integrity by preventing the creation of orphaned records in the Books table.

The foreign key constraint enforces the relationship, while the ON DELETE NO ACTION option specifies that deletions in the Authors table should be restricted if related records exist.

Other Options

As mentioned, there are other options that we can use with ON DELETE. They are:

• CASCADE: Propagates the delete operation to the foreign key in the child table. In other words, it deletes the related row(s) in the child table (in addition to the row in the parent table).
• SET NULL: Sets the foreign key in the child table to NULL.
• SET DEFAULT: Sets the foreign key in the child table to its default value.

Summary

The ON DELETE NO ACTION option should be used on your foreign key constraints whenever you need to ensure that a parent record cannot be deleted if it has dependent records in a related table. This maintains data integrity by preventing orphaned records in the child table.