This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

A FOREIGN KEY Constraint is either a < Table Constraint> or a <Column Constraint> and defines a rule that constrains a foreign key to values that match only those values contained in a referenced unique key. The required syntax for a FOREIGN KEY Constraint is:

FOREIGN KEY <Table Constraint> ::=
[ CONSTRAINT <Constraint name> ]  
FOREIGN KEY (referencing <Column name> [ {,<Column name>}... ]) 
   REFERENCES referenced <Table name> 
      [ (referenced <Column name> [ {,<Column name>}... ]) ] 
   [ MATCH {FULL | PARTIAL | SIMPLE} ] 
   [ <referential triggered action> ] 
[ <constraint attributes> ] 

      <referential triggered action> ::=
      ON UPDATE <action> [ ON DELETE <action> ] | 
      ON DELETE <action> [ ON UPDATE <action> ]

         <action> ::=
         NO ACTION | 
         CASCADE | 
         RESTRICT | 
         SET NULL | 
         SET DEFAULT

FOREIGN KEY <Column Constraint> ::= 
[ CONSTRAINT <Constraint name> ] 
<Column name> REFERENCES referenced <Table name> 
      [ (referenced <Column name>) ]
   [ MATCH {FULL | PARTIAL | SIMPLE} ] 
   [ <referential triggered action> ] 
[ <constraint attributes> ]

A Base table may be constrained by zero or more FOREIGN KEY Constraints, which specify a rule that a group of one or more Columns of the Table may contain only those values found in a similar set of unique Columns belonging to (usually) another Table. You can't define a foreign key with Columns that have a <data type> of BLOB, CLOB, NCLOB, or ARRAY. Here are some examples of FOREIGN KEY Constraint definitions:

CREATE TABLE Table_2 ( 
  column_1 SMALLINT, 
  CONSTRAINT constraint_1 FOREIGN KEY(column_1) REFERENCES Table_1 
      NOT DEFERRABLE); 
-- defines a FOREIGN KEY <Table Constraint> in CREATE TABLE

CREATE TABLE Table_2 ( 
  column_1 SMALLINT CONSTRAINT constraint_1 
      FOREIGN KEY REFERENCES Table_1 NOT DEFERRABLE,  
  column_2 CHAR(5));
-- defines a FOREIGN KEY <Column Constraint> in CREATE TABLE 

ALTER TABLE Table_2 ADD CONSTRAINT constraint_2 
  FOREIGN KEY(column_1,column_2) REFERENCES Table_1(column_3,column_5) 
      DEFERRABLE INITIALLY IMMEDIATE;
-- defines a FOREIGN KEY <Table Constraint> in ALTER TABLE

Once created, a FOREIGN KEY <Column Constraint> logically becomes a FOREIGN KEY <Table Constraint>. The <Column Constraint> in this SQL statement:

CREATE TABLE Table_2 ( 
  column_1 SMALLINT REFERENCES Table_1);

is therefore equivalent to the <Table Constraint> in this SQL statement:

CREATE TABLE Table_2 ( 
  column_1 SMALLINT, 
  FOREIGN KEY(column_1) REFERENCES Table_1);

The rationale for a foreign key is: you can't have an employee in department D if there is no department D, you can't have a branch that produces Widgets if you don't have a product called a Widget, you can't locate an office in state = 'TY' if there is no state named 'Tynnessee'. A FOREIGN KEY Constraint forges a link between the referencing Table and the referenced Table: it makes it impossible to COMMIT any operation that would cause the foreign key to contain any values that are not found in the referenced unique key. (The referencing Table is the Table that the FOREIGN KEY Constraint belongs to; the foreign key itself is made up of one or more Columns of that Table: these are called the referencing Columns. The referenced Table is the Table that contains the unique key that the foreign key must match: the Columns that make up that unique key are called the referenced Columns. SQL allows the referencing Table and the referenced Table to be the same.) In the usual situation, illustrated in the examples above (other actions can be specified), the Constraint makes it impossible to drop TABLE_1 (because TABLE_2 references it), or to delete or change a row in TABLE_1 so that TABLE_2 is left with unmatched referencing values, or to insert a row into TABLE_2 unless its referencing values are matched somewhere in TABLE_1. A FOREIGN KEY Constraint is violated if its condition is FALSE for any row of the Table it belongs to. The result of the evaluation of the FOREIGN KEY Constraint condition depends on the presence of null values and the degree of matching specified for the Constraint; see the comments on the MATCH clause, later in this section.

Referencing Columns

The FOREIGN KEY clause of a FOREIGN KEY <Table Constraint> definition names the referencing Columns: the group of one or more Columns that make up the foreign key (a Column may appear in the list only once). You may specify only unqualified <Column name>s in this clause.

Referenced Table and Columns

The REFERENCES clause of a FOREIGN KEY Constraint definition names the referenced Base table: the Base table that contains the referenced unique key. The Table types must match: if the Table that owns the FOREIGN KEY Constraint is a persistent Base table, the referenced Table must also be a persistent Base Table; if the referencing Table is a GLOBAL TEMPORARY Base table, the referenced Table must also be a GLOBAL TEMPORARY Base Table; if the referencing Table is a created LOCAL TEMPORARY Base table, the referenced Table must be either a GLOBAL TEMPORARY Base Table or a created LOCAL TEMPORARY Base table; if the referencing Table is a declared LOCAL TEMPORARY Base table, the referenced Table must be either a GLOBAL TEMPORARY Base Table, a created LOCAL TEMPORARY Base table or a declared LOCAL TEMPORARY Base table; and if the referencing Table is any temporary Base table defined with an ON COMMIT DELETE ROWS clause, the referenced Table must also be a temporary Base Table defined with that clause.

The referenced Columns, optionally named in the REFERENCES clause of a FOREIGN KEY Constraint definition, are the group of one or more Columns that make up the referenced unique key (that is, the referenced Columns must be named in a NOT DEFERRABLE UNIQUE or NOT DEFERRABLE PRIMARY KEY Constraint that belongs to the referenced Table and may therefore appear in the list only once). You may specify only unqualified <Column name>s in this clause. The Columns in the foreign key must match the number of, and have a comparable <data type> with, the corresponding Columns in the referenced unique key. If you omit the referenced Columns list from a FOREIGN KEY Constraint definition, the referenced Table must be constrained by a NOT DEFERRABLE PRIMARY KEY Constraint; the primary key is also the referenced unique key.

Here are some more examples of FOREIGN KEY Constraint definitions:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  column_2 VARCHAR(4), 
  CONSTRAINT constraint_1 
      PRIMARY KEY(column_1,column_2) NOT DEFERRABLE); 

CREATE TABLE Table_2 ( 
  column_1 SMALLINT, 
  column_2 VARCHAR(4), 
  CONSTRAINT constraint_2 
      FOREIGN KEY(column_1,column_2) REFERENCES Table_1); 
-- Here the referenced unique key defaults to Table_1's primary key

CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  column_2 VARCHAR(4), 
  CONSTRAINT constraint_1 
      PRIMARY KEY(column_1,column_2) NOT DEFERRABLE); 

CREATE TABLE Table_2 ( 
  column_1 SMALLINT, 
  column_2 VARCHAR(4), 
  CONSTRAINT constraint_2 
      FOREIGN KEY(column_1,column_2) 
        REFERENCES Table_1(column_1,column_2));
-- Here the foreign key explicitly matches Table_1's primary key

CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  column_2 VARCHAR(4), 
  column_3 DATE, 
  CONSTRAINT constraint_1 
      PRIMARY KEY(column_1,column_2) NOT DEFERRABLE, 
  CONSTRAINT constraint_2 
      UNIQUE(column3) NOT DEFERRABLE);

CREATE TABLE Table_2 ( 
  column_1 SMALLINT, 
  column_2 VARCHAR(4), 
  column_3 DATE, 
  CONSTRAINT constraint_3 
      FOREIGN KEY(column3) REFERENCES Table_1(column3));
-- Here the foreign key explicitly matches Table_1's unique key; this is
mandatory because, if the referenced Column list were omitted, your DBMS would
attempt to match the foreign key (COLUMN_3) to Table_1's primary key
(COLUMN_1,COLUMN_2) and would fail.

Privileges

In order to create a FOREIGN KEY Constraint, the <AuthorizationID> that owns the referencing Table must be the current <AuthorizationID> and must have the REFERENCES Privilege on every referenced Column named.

MATCH clause

The optional MATCH clause of a FOREIGN KEY Constraint definition specifies the degree of the required match between the values of the foreign key and the referenced unique key. There are three match options: MATCH SIMPLE, MATCH FULL, and MATCH PARTIAL. If you omit the MATCH clause, it defaults to MATCH SIMPLE. For example, these two SQL statements are equivalent:

CREATE TABLE Table_2 ( 
  column_1 SMALLINT,
  CONSTRAINT constraint_1 REFERENCES Table_1);

CREATE TABLE Table_2 ( 
  column_1 SMALLINT,
  CONSTRAINT constraint_1 REFERENCES Table_1 MATCH SIMPLE);

The MATCH option specified for a FOREIGN KEY Constraint has an effect only when your foreign key contains null values.

For MATCH SIMPLE, a FOREIGN KEY Constraint is satisfied if, for each row of the referencing Table, either (a) at least one of the foreign key Columns is NULL or (b) none of the foreign key Columns is NULL and the value of the entire foreign key equals the value of the entire unique key in at least one row of the referenced Table. For example, given a referenced Table with these two unique key rows:

  • {10,'tiny'} {20,'huge'}

these foreign key rows are valid for the referencing Table:

{10,'tiny'} -- because a matching unique key row exists
{NULL,'tiny'} {10,NULL} {NULL,'soso'} {30,NULL} -- because, in each case, one foreign key Column is NULL

and this foreign key row is invalid:

{10,'huge'} -- because no matching unique key row exists

For MATCH FULL, a FOREIGN KEY Constraint is satisfied if, for each row of the referencing Table, either (a) every foreign key Column is NULL or (b) none of the foreign key Columns is NULL and the value of the entire foreign key equals the value of the entire unique key in at least one row of the referenced Table. (If you define a FOREIGN KEY Constraint with MATCH FULL and there is either (a) only one Column in the foreign key or (b) one or more Columns defined as NOT NULL in the foreign key, then the Constraint will have the same effect as if you had defined the Constraint with MATCH SIMPLE.) For example, given a referenced Table with these two unique key rows:

  • {10,'tiny'} {20,'huge'}

these foreign key rows are valid for the referencing Table:

{10,'tiny'} -- because a matching unique key row exists
{NULL,NULL} -- because the entire foreign key is NULL

and these foreign key rows are invalid:

{10,'huge'} -- because no matching unique key row exists
{NULL,'tiny'} {10,NULL} -- because, in each case, only some of the foreign key is NULL

For MATCH PARTIAL, a FOREIGN KEY Constraint is satisfied if, for each row of the referencing Table, at least one foreign key Column is NULL and the values of the rest of the foreign key Columns equal the values of the corresponding unique key Columns in at least one row of the referenced Table. (If you define a FOREIGN KEY Constraint with MATCH PARTIAL and there is either (a) only one Column in the foreign key or (b) one or more Columns defined as NOT NULL in the foreign key, then the Constraint will have the same effect as if you had defined the Constraint with MATCH SIMPLE.) For example, given a referenced Table with these two unique key rows:

  • {10,'tiny'} {20,'huge'}

these foreign key rows are valid for the referencing Table:

{10,'tiny'} -- because a matching unique key row exists
{NULL,NULL} -- because the entire foreign key is NULL
{NULL,'tiny'} {10,NULL} {NULL,'huge'} {20,NULL} -- because, in each case, one foreign key Column is NULL but the other matches the corresponding unique Column in some row of the referenced Table

and these foreign key rows are invalid:

{10,'huge'} -- because no matching unique key row exists
{NULL,'big'} {30,NULL} -- because, although one foreign key Column is NULL. the other does not match the value of the corresponding unique Column in any row of the referenced Table

TIP: Use MATCH FULL, or define all foreign key Columns with a NOT NULL Constraint.

Referential Action

What happens if you UPDATE a primary key? What happens if you DELETE a primary key? Neither should happen often, but if you must, remember that the rule for primary/foreign key relationships is in terms of database states: "no foreign key shall dangle". There are two ways to get rid of a dangling key: prevent it from happening in the first place, or compensate if it does happen. You can do this by defining your FOREIGN KEY Constraints with one ON UPDATE clause and/or one ON DELETE clause, in any order. The optional ON UPDATE clause specifies the action you want your DBMS to take when an UPDATE operation on the referenced Table causes the FOREIGN KEY Constraint to be violated. The optional ON DELETE clause specifies the action you want your DBMS to take when a DELETE operation on the referenced Table causes the FOREIGN KEY Constraint to be violated. If you omit either clause, both default to ON UPDATE NO ACTION and ON DELETE NO ACTION. For example, these two SQL statements are equivalent:

CREATE TABLE Table_2 ( 
  column_1 SMALLINT,
  CONSTRAINT constraint_1 REFERENCES Table_1); 

CREATE TABLE Table_2 ( 
  column_1 SMALLINT,
  CONSTRAINT constraint_1 REFERENCES Table_1 
      ON UPDATE NO ACTION ON DELETE NO ACTION);

Besides NO ACTION, you may also specify these actions in the ON UPDATE and ON DELETE clauses: RESTRICT, CASCADE, SET NULL, and SET DEFAULT. To decide which to use, consider first what you would like to happen. Should you be prevented from leaving a dangling reference or should you change the dangling reference too? (A dangling reference is a foreign key that doesn't point to a unique key any more, and it isn't allowed in SQL.) If you do change the dangling reference, should you be changing to (a) the same value as the new unique key, (b) NULL or (c) some other value? Or should the change be a deletion? All these options are available. The action taken by your DBMS in all cases depends on the definition of "matching rows" for the FOREIGN KEY Constraint: this, in turn, depends on the FOREIGN KEY Constraint's MATCH option.

For MATCH SIMPLE and MATCH FULL, given a row in the referenced Table, every row in your referencing Table that contains a foreign key whose value equals the value of that unique key, is a matching row. For MATCH PARTIAL, given a row in the referenced Table, every row in your referencing Table that contains a foreign key with at least one non-null Column whose value equals the value of that unique key, is a matching row and a matching row that matches only one row of the referenced Table is a unique matching row.

  • If you define a FOREIGN KEY Constraint with MATCH SIMPLE ON DELETE CASCADE or with MATCH FULL ON DELETE CASCADE, every time you DELETE rows from the referenced Table, your DBMS will also DELETE all matching rows from the referencing Table. If you define a FOREIGN KEY Constraint with MATCH PARTIAL ON DELETE CASCADE, every time you DELETE rows from the referenced Table, your DBMS will also DELETE all unique matching rows from the referencing Table.
  • If you define a FOREIGN KEY Constraint with MATCH SIMPLE ON DELETE SET NULL or with MATCH FULL ON DELETE SET NULL, every time you DELETE rows from the referenced Table, your DBMS will also set the entire foreign key in every matching row of the referencing Table to NULL. If you define a FOREIGN KEY Constraint with MATCH PARTIAL ON DELETE SET NULL, every time you DELETE rows from the referenced Table, your DBMS will also set the entire foreign key in every unique matching row of the referencing Table to NULL.
  • If you define a FOREIGN KEY Constraint with MATCH SIMPLE ON DELETE SET DEFAULT or with MATCH FULL ON DELETE SET DEFAULT, every time you DELETE rows from the referenced Table, your DBMS will also set each Column of the foreign key in every matching row of the referencing Table to its default value. If you define a FOREIGN KEY Constraint with MATCH PARTIAL ON DELETE SET DEFAULT, every time you DELETE rows from the referenced Table, your DBMS will also set each Column of the foreign key in every unique matching row of the referencing Table to its default value.
  • If you define a FOREIGN KEY Constraint with MATCH SIMPLE ON DELETE RESTRICT or with MATCH FULL ON DELETE RESTRICT, every time you attempt to DELETE rows from the referenced Table, your DBMS will check for matching rows in the referencing Table. If you define a FOREIGN KEY Constraint with MATCH PARTIAL ON DELETE RESTRICT, every time you attempt to DELETE rows from the referenced Table, your DBMS will check for unique matching rows in the referencing Table. In either case, if any matching (or unique matching, as appropriate) rows exist, the operation will fail: your DBMS will return the SQLSTATE error 23001 "integrity constraint violation-restrict violation". A FOREIGN KEY Constraint defined with ON DELETE NO ACTION acts essentially the same as one defined with ON DELETE RESTRICT.
  • If you define a FOREIGN KEY Constraint with MATCH SIMPLE ON UPDATE CASCADE or with MATCH FULL ON UPDATE CASCADE, every time you UPDATE a referenced Column, your DBMS will also UPDATE the corresponding foreign key Column in all matching rows of the referencing Table to the same value. If you define a FOREIGN KEY Constraint with MATCH PARTIAL ON UPDATE CASCADE, every time you UPDATE a referenced Column, your DBMS will also UPDATE any corresponding non-null foreign key Column in every unique matching row of the referencing Table to the same value provided that, for each referencing row changed, all rows of the referenced Table that considered that referencing row to be a matching row also have the same change made. If this isn't the case, the operation will fail: your DBMS will return the SQLSTATE error 27000 "triggered data change violation".
  • If you define a FOREIGN KEY Constraint with MATCH SIMPLE ON UPDATE SET NULL, every time you UPDATE a referenced Column, your DBMS will also set the corresponding foreign key Column in all matching rows of the referencing Table to NULL. If you define a FOREIGN KEY Constraint with MATCH FULL ON UPDATE SET NULL, every time you UPDATE a referenced Column, your DBMS will also set the entire foreign key in every matching row of the referencing Table to NULL. If you define a FOREIGN KEY Constraint with MATCH PARTIAL ON UPDATE SET NULL, every time you UPDATE a referenced Column, your DBMS will also set any corresponding non-null foreign key Column in every unique matching row of the referencing Table to NULL.
  • If you define a FOREIGN KEY Constraint with MATCH SIMPLE ON UPDATE SET DEFAULT or with MATCH FULL ON UPDATE SET DEFAULT, every time you UPDATE a referenced Column, your DBMS will also set the corresponding foreign key Column in all matching rows of the referencing Table to its default value. If you define a FOREIGN KEY Constraint with MATCH PARTIAL ON UPDATE SET DEFAULT, every time you UPDATE a referenced Column, your DBMS will also set any corresponding non-null foreign key Column in every unique matching row of the referencing Table to its default value.
  • If you define a FOREIGN KEY Constraint with MATCH SIMPLE ON UPDATE RESTRICT or with MATCH FULL ON UPDATE RESTRICT, every time you attempt to UPDATE a referenced Column, your DBMS will check for matching rows in the referencing Table. If you define a FOREIGN KEY Constraint with MATCH PARTIAL ON UPDATE RESTRICT, every time you attempt to UPDATE a referenced Column, your DBMS will check for unique matching rows in the referencing Table. In either case, if any matching (or unique matching, as appropriate) rows exist, the operation will fail: your DBMS will return the SQLSTATE error 23001 "integrity constraint violation-restrict violation". A FOREIGN KEY Constraint defined with ON UPDATE NO ACTION acts essentially the same as one defined with ON UPDATE RESTRICT.

For an example of the NO ACTION/RESTRICT option, consider the following SQL statements:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT DEFAULT 12 
      CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE);

CREATE TABLE Table_2 ( 
  column_1 SMALLINT DEFAULT 15 
      CONSTRAINT constraint_2 FOREIGN KEY(column_1) REFERENCES Table_1 
        MATCH FULL 
        ON UPDATE NO ACTION ON DELETE NO ACTION 
        NOT DEFERRABLE); 

INSERT INTO Table_1 VALUES(10); 

INSERT INTO Table_1 VALUES(15); 

INSERT INTO Table_2 VALUES(10);

For TABLE_1 and TABLE_2, the effect of each of these SQL statements:

UPDATE Table_1 SET column_1=11 WHERE column_1=10; 

UPDATE Table_2 SET column_1=11 where column_1=10; 

INSERT INTO Table_2 VALUES(11);

is an error return, because the result in each case would be a value in TABLE_2.COLUMN_1 that does not match some value in TABLE_1.COLUMN_1.

NOTE: The action specified for the ON UPDATE clause has no effect on UPDATE operations or INSERT operations performed on the referencing Table. Thus, an INSERT operation that attempts to put a row into TABLE_2, or an UPDATE operation that attempts to change a row of TABLE_2, will always fail if the resulting value in TABLE_2.COLUMN_1 does not match some value of TABLE_1.COLUMN_1.

The effect of this SQL statement:

DELETE FROM Table_1 WHERE column_1=10;

is also an error return, because deleting the applicable row from TABLE_1 would leave TABLE_2 with a row containing a COLUMN_1 value that does not match any TABLE_1.COLUMN_1 value.

To summarize:

  • When an UPDATE operation attempts to update a non-null value in a Column that is referenced in a FOREIGN KEY Constraint defined with ON UPDATE NO ACTION or ON UPDATE RESTRICT, the UPDATE fails, regardless of the MATCH option, if there are matching rows in the referencing Table.
  • When a DELETE operation attempts to delete a row from a Table that is referenced in a FOREIGN KEY Constraint defined with ON DELETE NO ACTION or ON DELETE RESTRICT, the DELETE operation fails, regardless of the MATCH option, if there are matching rows in the referencing Table.

For an example of the CASCADE option, consider the following SQL statements:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT DEFAULT 12 
      CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE);

CREATE TABLE Table_2 ( 
  column_1 SMALLINT DEFAULT 15 
      CONSTRAINT constraint_2 FOREIGN KEY(column_1) REFERENCES Table_1 
        MATCH FULL 
        ON UPDATE CASCADE ON DELETE CASCADE 
        NOT DEFERRABLE); 

INSERT INTO Table_1 VALUES(10); 

INSERT INTO Table_1 VALUES(15); 

INSERT INTO Table_2 VALUES(10);

For TABLE_1 and TABLE_2, the effect of this SQL statement:

UPDATE Table_1 SET column_1=11 where column_1=10;

is that all values of TABLE_1.COLUMN_1 that are equal to 10 are set to 11, with the same effect cascading down; that is, all values in TABLE_2.COLUMN_1 that are equal to 10 are also set to 11. And the effect of this SQL statement:

DELETE FROM Table_1 WHERE column_1=10;

is that all applicable rows are deleted from TABLE_1, with the same effect cascading down; that is, all matching rows of TABLE_2 are also deleted.

To summarize:

  • When an UPDATE operation attempts to update a non-null value in a Column that is referenced in a FOREIGN KEY Constraint defined with MATCH SIMPLE or MATCH FULL and ON UPDATE CASCADE, the referenced Column, and the corresponding referencing Column in all matching rows, are set to the new value. When an UPDATE operation attempts to update a non-null value in a Column that is referenced in a FOREIGN KEY Constraint defined with MATCH FULL and ON UPDATE CASCADE, the referenced Column, and the corresponding referencing Column in all unique matching rows where the referencing Column contains a non-null value, are set to the new value. Unique matching rows with a referencing Column that contains the null value are not updated.
  • When a DELETE operation attempts to delete a row from a Table that is referenced in a FOREIGN KEY Constraint defined with MATCH SIMPLE or MATCH FULL and ON DELETE CASCADE, the applicable row, and all matching rows, are deleted. When a DELETE operation attempts to delete a row from a Table that is referenced in a FOREIGN KEY Constraint defined with MATCH PARTIAL and ON DELETE CASCADE, the applicable row, and all unique matching rows, are deleted.

For an example of the SET NULL option, consider the following SQL statements:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT DEFAULT 12 
      CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE);

CREATE TABLE Table_2 ( 
  column_1 SMALLINT DEFAULT 15 
      CONSTRAINT constraint_2 FOREIGN KEY(column_1) REFERENCES Table_1 
        MATCH FULL 
        ON UPDATE SET NULL ON DELETE SET NULL 
        NOT DEFERRABLE); 

INSERT INTO Table_1 VALUES(10); 

INSERT INTO Table_1 VALUES(15); 

INSERT INTO Table_2 VALUES(10);

For TABLE_1 and TABLE_2, the effect of this SQL statement:

UPDATE Table_1 SET column_1=11 where column_1=10;

is that all values of TABLE_1.COLUMN_1 that are equal to 10 are set to 11, and that all values in TABLE_2.COLUMN_1 that are equal to 10 are set to the null value. (If TABLE_2.COLUMN_1 did not allow null values, the UPDATE statement would fail.) And the effect of this SQL statement:

DELETE FROM Table_1 WHERE column_1=10;

is that all applicable rows are deleted from TABLE_1, and that all values in TABLE_2.COLUMN_1 that are equal to 10 are set to the null value. (If TABLE_2.COLUMN_1 did not allow null values, the DELETE statement would fail.)

To summarize:

  • When an UPDATE operation attempts to update a non-null value in a Column that is referenced in a FOREIGN KEY Constraint defined with MATCH SIMPLE and ON UPDATE SET NULL, the referenced Column is set to the new value and the corresponding referencing Column in all matching rows is set to the null value. When an UPDATE operation attempts to update a non-null value in a Column that is referenced in a FOREIGN KEY Constraint defined with MATCH FULL and ON UPDATE SET NULL, the referenced Column is set to the new value and every referencing Column (not just the corresponding Column) in all matching rows is set to the null value. When an UPDATE operation attempts to update a non-null value in a Column that is referenced in a FOREIGN KEY Constraint defined with MATCH PARTIAL and ON UPDATE SET NULL, the referenced Column is set to the new value and the corresponding referencing Column in all unique matching rows is set to the null value.
  • When a DELETE operation attempts to delete a row from a Table that is referenced in a FOREIGN KEY Constraint defined with MATCH SIMPLE or MATCH FULL and ON DELETE SET NULL, the applicable row is deleted and, for all matching rows, each referencing Column is set to the null value. When a DELETE operation attempts to delete a row from a Table that is referenced in a FOREIGN KEY Constraint defined with MATCH PARTIAL and ON DELETE SET NULL, the applicable row is deleted, and, for all unique matching rows, each referencing Column is set to the null value.

For an example of the SET DEFAULT option, consider the following SQL statements:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT DEFAULT 12 
      CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE);

CREATE TABLE Table_2 ( 
  column_1 SMALLINT DEFAULT 15 
      CONSTRAINT constraint_2 FOREIGN KEY(column_1) REFERENCES Table_1 
        MATCH FULL 
        ON UPDATE SET DEFAULT ON DELETE SET DEFAULT 
        NOT DEFERRABLE); 

INSERT INTO Table_1 VALUES(10); 

INSERT INTO Table_1 VALUES(15); 

INSERT INTO Table_2 VALUES(10);

For TABLE_1 and TABLE_2, the effect of this SQL statement:

UPDATE Table_1 SET column_1=11 where column_1=10;

is that all values of TABLE_1.COLUMN_1 that are equal to 10 are set to 11, and that all values of TABLE_2.COLUMN_1 that are equal to 10 are set to COLUMN_1's default value, 15. (If no row existed where the value of TABLE_1.COLUMN_1 was 15, the UPDATE statement would fail.) And the effect of this SQL statement:

DELETE FROM Table_1 WHERE column_1=10;

is that all applicable rows are deleted from TABLE_1 and that all values in TABLE_2.COLUMN_1 that are equal to 10 are set to COLUMN_1's default value, 15. (If no row existed where the value of TABLE_1.COLUMN_1 was 15, the DELETE statement would fail.)

To summarize:

  1. When an UPDATE operation attempts to update a non-null value in a Column that is referenced in a FOREIGN KEY Constraint defined with MATCH SIMPLE or MATCH FULL and ON UPDATE SET DEFAULT, the referenced Column is set to the new value and the corresponding referencing Column in all matching rows is set to its default value. When an UPDATE operation attempts to update a non-null value in a Column that is referenced in a FOREIGN KEY Constraint defined with MATCH PARTIAL and ON UPDATE SET DEFAULT, the referenced Column is set to the new value and the corresponding referencing Column in all unique matching rows is set to its default value.
  2. When a DELETE operation attempts to delete a row from a Table that is referenced in a FOREIGN KEY Constraint defined with MATCH SIMPLE or MATCH FULL and ON DELETE SET DEFAULT, the applicable row is deleted, and, for all matching rows, each referencing Column is set to its default value. When a DELETE operation attempts to delete a row from a Table that is referenced in a FOREIGN KEY Constraint defined with MATCH PARTIAL and ON DELETE SET DEFAULT, the applicable row is deleted, and, for all unique matching rows, each referencing Column is set to its default value.

NOTE: It is not possible to update the same Column more than once in a single SQL statement. If such an operation is attempted, the statement will fail: your DBMS will return the SQLSTATE error 27000 "triggered data change violation".


NOTE: All rows that are to be deleted by an SQL statement are effectively deleted at the end of that statement's execution, prior to the checking of any integrity constraints.

If you want to restrict your code to Core SQL, don't define your FOREIGN KEY Constraints with a MATCH clause, an ON UPDATE clause or an ON DELETE clause.

Comments

Comments loading...