SQL Database Design » 10. Constraints » 10.2 Foreign Keys

10.2 Foreign Keys

Table of Contents

Foreign key constraints represent references to other tables. Foreign keys may be NULL, in which case the relationship is optional. Foreign keys allow the specification of rules and actions along with them to ensure what is called referential integrity. Referential integrity rules always refer to the referenced table's primary or candidate key.

10.2.1 Integrity Rules

There are two rules: either a referenced table's row (primary key) is updated or the row is deleted. There are two rules that can be specified along with foreign key constraints:

  • ON UPDATE <action>
  • ON DELETE <action>

You should always specify these rules for each foreign key constraint explicitly. Otherwise, the DBMS's default action is put in place, which might not be what you want. You have to check your DBMSs manual for the concrete behavior.

10.2.2 Integrity Actions

The following actions are defined by the SQL standard:

  • NO ACTION
  • RESTRICT
  • CASCADE
  • SET NULL
  • SET DEFAULT

Usually, if no action is specified, a foreign key's action defaults to NO ACTION for both ON UPDATE and ON DELETE. In practice, you often find situations, where you have differing rules for updating and deleting.

10.2.2.1 NO ACTION

One might really think that no action is taken when a referenced row gets updated or deleted and that the rows referencing the affected row now "point" to nowhere. This is not true. Such behavior would essentialy break referential integrity - definitely not what these rules were created for. I personally think NO ACTION does not define a good action - at least it is all but intuitive. NO ACTION implies that absolutely no action is taken, which is misleading.

The MySQL manual states the following about NO ACTION: In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary key value is not allowed to proceed if there is a related foreign key value in the referenced table. (Maybe this was the reason why RESTRICT was made exactly equivalent to the NO ACTION action on all DBMS I have examined.)

NO ACTION disallows to delete or update a row referenced by other rows. An error is generated in an attempt to do so. The only way to delete or update rows that have a NO ACTION action attached to them is to have no more rows that actually reference the affected row. You would have to get rid of the referencing rows first. As such, NO ACTION enforces bottom-up deletion, as opposed to the top-down CASCADE action. The only use case for the ON UPDATE NO ACTION combination I can think of are constant (read-only), tree-like data structures (ones that have a number of referencing "child" rows).

10.2.2.2 RESTRICT

The RESTRICT keyword has been a reserved keyword in SQL-92 and SQL:1999. It was made a non-reserved keyword in SQL:2003. All other rules' keywords have been reserved since SQL-92. If you look at the SQL-92 BNF notation here, you will notice that the RESTRICT rule is missing. It seems like it is a standard SQL keyword only because it is used as a constraint for the DROP DATABASE statement. To quote an email communication I once had: I think that the RESTRICT option was omitted from the SQL-92 standard for reasons now unknown, but the omission was fixed in SQL:1999.

Here is what I received in another email: RESTRICT .. isn't exactly equivalent to NO ACTION; it is more like 'REJECT' (as in disallow). I don't think that's what NO ACTION means. There is a rather lengthy and subtle difference between the two concerning the MATCH clause. It is hidden in the SQL-92 BNF, section 11.8 <referential constraint definition>, p. 231:

5) If a <delete rule> is specified and a row of the referenced table
   that has not previously been marked for deletion is marked for deletion,
   then
   Case:
   
    a) If <match type> is not specified or if FULL is specified, then
       Case:
        i)   If the <delete rule> specifies CASCADE, then all matching
             rows are marked for deletion.
        ii)  If the <delete rule> specifies SET NULL, then in all
             matching rows each referencing column is set to the null value.
        iii) If the <delete rule> specifies SET DEFAULT, then in all
             matching rows each referencing column is set to the default
             value specified in the General Rules of Subclause 11.5,
             "<default clause>".

    b) If PARTIAL is specified, then
       Case:
        i)   If the <delete rule> specifies CASCADE, then all unique
             matching rows are marked for deletion.
        ii)  If the <delete rule> specifies SET NULL, then in all
             unique matching rows each referencing column is set to the null
             value.
        iii) If the <delete rule> specifies SET DEFAULT, then in all
             unique matching rows each referencing column is set to the
             default value specified in the General Rules of Subclause 11.5,
             "<default clause>".
        
    Note: Otherwise, the <referential action> is not performed.

Notice the note at the end. I have not found the time to investigate the issue, so I cannot summarize their difference. Instead, I will quote the email I received: SQL:1999 added RESTRICT as a <referential action> which prevents a delete or update if there is a matching row.

The RESTRICT action is absolutely equivalent to NO ACTION on PostgreSQL, MySQL, Oracle, and SQL Server. It seems as if the difference between the two is too obscure to be of practical value: [the] IBM manual says "There are very few cases this will make a difference...". Another source I found writes: If your DBMS (like DB2) supports both rules, the only difference is in the timing of the application of each rule. The DBMS enforces the RESTRICT rule before any other column constraints and enforces the NO ACTION rule after enforcing other column constraints. In almost all cases, the two rules operate identically.

Although NO ACTION is the original SQL-92 action, RESTRICT better expresses the behavior. While I recommend to use RESTRICT over NO ACTION, SQL Server does not seem to support the RESTRICT action (but Sybase's Transact-SQL does). NO ACTION is obviously less intuitive, but it appears to be more widely implemented.

10.2.2.3 CASCADE

Deletes or updates all rows referencing the affected row. Probably the most frequently used rule around. It is the de facto referential integrity action. Note again, that there is no problem in using differing actions with ON UPDATE and ON DELETE.

10.2.2.4 SET NULL

Assigns a NULL value to all rows referencing the affected row. Of course, this rule can only be applied to columns that do not have a NOT NULL constraint.

10.2.2.5 SET DEFAULT

Assigns the default value to all rows referencing the affected row. To my surprise, MySQL and SQL Server do not implement the SET DEFAULT rule. On MySQL, the InnoDB table engine does not support it: SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

The reason as to why SET DEFAULT does not work with MySQL and SQL Server is more than incomprehensible. If the specified default value was not found in the referenced table, you could just generate an error and rollback, like most other DBMSs do. Entering a default value is no different from entering any value manually. It is not acceptable from a technical perspective.

10.2.3 Summary

This is the syntactical side of foreign keys. I will go into detail on how apply referential integrity constraints to real-world questions in the 13. Design Heuristics section.

References for Foreign Keys:

Last updated: 2010-08-04