CODEWITHSUNDEEP
sqlforeign-keysconstraints
lucidbrot
lucidbrot

Reputation: 6158

SQL Foreign Key without constraints the same as ON DELETE RESTRICT?

I found the following two SQL schemas in an old exam. The exam does not seem to stick with one specific SQL syntax, but I hope this shouldn't matter for this question: What is the difference between ON DELETE RESTRICT and having just a Foreign Key without any such trigger clause?

-- Schema B --
CREATE TABLE tab1 (
tab1_key INT
NOT NULL UNIQUE ) ;
CREATE TABLE tab2 (
tab2_ref INT NOT NULL,
FOREIGN KEY ( tab2_ref )
REFERENCES tab1 ( tab1_key ) ) ;

-- Schema C --
CREATE TABLE tab1 (
tab1_key INT
NOT NULL UNIQUE ) ;
CREATE TABLE tab2 (
tab2_ref INT NOT NULL,
FOREIGN KEY ( tab2_ref )
REFERENCES tab1 ( tab1_key )
ON DELETE RESTRICT
ON UPDATE NO ACTION ) ;

I would assume that the only difference between those two schemas is the last line in Schema C, but I cannot find any documentation on using Foreign Key without any additional constraint. So is this assumption correct?

I attempted to try those in pgAdmin, but that program keeps failing (and seems to be known for its bugs) so I figured asking here is less effort than debugging that tool.

Also, please note that while these Schemas are from an old exam, this question is not the same as the exam question. Otherwise I would look at the solutions. This is not homework.

Upvotes: 0

Views: 4737

Answers (2)

Chris Johnson
Chris Johnson

Reputation: 21976

In Postgres and every other DBMS I've used, the default ON DELETE action is RESTRICT. However this can differ by DBMS and version, e.g. RESTRICT was not supported by Microsoft SQL Server 2012 and earlier. It can also differ by DBMS configuration, e.g. MySQL has a setting foreign_key_checks that can disable foreign key enforcement actions. So you are wise to verify.

It would be easy enough to create two tables in your DBMS and test its actual behavior.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

From Using Foreign Key constraints:

  • RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

  • NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT... Some database systems have deferred checks, and NO ACTION is a deferred check.

(My emphasis)

So in fact there's no real difference between the two, unless you're using a database system that supports deferred constraints.

Upvotes: 2

Related Questions