CODEWITHSUNDEEP
sql-serverforeign-keys
Piero Alberto
Piero Alberto

Reputation: 3943

Forbid delete if the record is referred from other tables

In SQL Server 2008, how can I forbid the delete (or throw an exception) if the involved record is used from others tables in some foreign keys?

I have set, in the relationship, "on delete -> nothing", but it isn't what I need... I need to stop the delete...

Let's say, if I have the table books and the table authors, and in books I have idAuthor, how can I forbid the delete of an author, if I have at least one book written by him?

Upvotes: 2

Views: 665

Answers (2)

Bohemian
Bohemian

Reputation: 425208

For SQL Server use no action:

alter table ...
add foreign key
...
on delete no action

Other databases use restrict:

alter table ...
add foreign key
...
on delete restrict

In all cases, this option is the default and causes an exception to be thrown if the constraint would be violated by deletion of the parent row.

Upvotes: 1

Adrian Nasui
Adrian Nasui

Reputation: 1095

this happens by default in SQL Server - delete is not allowed for an entity that has foreign key rows (children) referencing it. So you should have "Enforce Foreign Key Constraints" set to YES in the Foreign Key Relation properties, by default.

This setting forces the server to throw an error when constraints are broken.

partial source

PS: I don't know where you are setting the "on delete -> nothing" in SQL Server.

Upvotes: 0

Related Questions