The Ultimate Guide to High Availability with MariaDB

Foreign Keys

You are viewing an old version of this article. View the current version here.

Overview

A foreign key is a constraint which can be used to enforce data integrity. It is composed by a column (or a set of columns) in a table called the child table, which references to a column (or a set of columns) in a table called the parent table. If foreign keys are used, MariaDB performs some checks to enforce that some integrity rules are always enforced.

Foreign keys can only be used with storage engines which support them. InnoDB and the obsolete PBXT support foreign keys.

Syntax

Foreign keys are created with CREATE TABLE or ALTER TABLE. The definition must follow this syntax:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

The symbol clause, if secified, is used in error messages and must be unique in the database.

The columns in the child table must be an index, or the leftmost part of an index. Index prefixes are not supported (thus, TEXT and BLOB columns cannot be used as foreign keys). The foreign key columns and the referenced columns must be of the same type.

Foreign keys can only be used with storage engines which support them. InnoDB and the obsolete PBXT support foreign keys. Also, the parent and the child table must use the same storage engine, and must not be TEMPORARY or partitioned tables. They can be the same table.

Constraints

If a foreign keys exists, each row in the child table must match a row in the parent table. MariaDB performs certain checks to guarantee that the data integrity is enforced:

  • Trying to insert non-matching rows produce an error.
  • When a row in the parent table is deleted and a child row exists, MariaDB performs an action which depends from the ON DELETE clause of the foreign key.
  • When a value in the column referenced by a foreign key changes and a child row exists, MariaDB performs an action which depends from the ON UPDATE clause of the foreign key.

The allowed actions for ON DELETE and ON UPDATE are:

  • RESTRICT: The change on the parent table is prevented. The statement terminates with an error. This is the default behavior for both ON DELETE and ON UPDATE.
  • NO ACTION: Synonym for RESTRICT.
  • CASCADE: The change is allowed and progragates on the child table. For example, if a parent row is deleted, the child row is also deleted; if a parent row's ID change, the child row's ID will also change.
  • SET NULL: The change is allowed, and the parent row's foreign key columns are set to NULL.
  • SET DEFAULT: Only worked with PBXT. Similar to SET NULL, but the foreign key columns were set to their default values.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.