Foreign Key Constraint

A foreign key is a column (or combination of columns) in a table whose values must match values of a column in some other table. The purpose of foreign keys is to enforce referential integrity, which essentially says that if column value A refers to column value B, then column value B must exist.

For example, given an orders table and a customers table, if you create a column orders.customer_id that references the customers.id primary key:

  • Each value inserted or updated in orders.customer_id must exactly match a value in customers.id, or be NULL.
  • Values in customers.id that are referenced by orders.customerid cannot be deleted or updated, unless you have cascading actions. However, values of customers.id that are _not present in orders.customer_id can be deleted or updated.

Details

Rules for creating foreign keys

Foreign Key Columns

  • Foreign key columns must use their referenced column's type.
  • Each column cannot belong to more than 1 Foreign Key constraint.
  • Cannot be a computed column.
  • Foreign key columns must be indexed. This is required because updates and deletes on the referenced table will need to search the referencing table for any matching records to ensure those operations would not violate existing references. In practice, such indexes are likely also needed by applications using these tables, since finding all records which belong to some entity, for example all orders for a given customer, is very common.

    • To meet this requirement when creating a new table, there are a few options:
      • New in v19.1: An index on the referencing columns is automatically created for you when you add a foreign key constraint to an empty table, if an appropriate index does not already exist. For an example, see Add the foreign key constraint with CASCADE.
      • Create indexes explicitly using the INDEX clause of CREATE TABLE.
      • Rely on indexes created by the PRIMARY KEY or UNIQUE constraints.
      • Have CockroachDB automatically create an index of the foreign key columns for you. However, it's important to note that if you later remove the Foreign Key constraint, this automatically created index is not removed.
      • Using the foreign key columns as the prefix of an index's columns also satisfies the requirement for an index. For example, if you create foreign key columns (A, B), an index of columns (A, B, C) satisfies the requirement for an index.
    • To meet this requirement when adding the Foreign Key constraint to an existing table, if the columns you want to constraint are not already indexed, use CREATE INDEX to index them and only then use the ADD CONSTRAINT statement to add the Foreign Key constraint to the columns.
      Referenced Columns
  • Referenced columns must contain only unique sets of values. This means the REFERENCES clause must use exactly the same columns as a UNIQUE or PRIMARY KEY constraint on the referenced table. For example, the clause REFERENCES tbl (C, D) requires tbl to have either the constraint UNIQUE (C, D) or PRIMARY KEY (C, D).

  • In the REFERENCES clause, if you specify a table but no columns, CockroachDB references the table's primary key. In these cases, the Foreign Key constraint and the referenced table's primary key must contain the same number of columns.

Null values

Single-column foreign keys accept null values.

Multiple-column (composite) foreign keys only accept null values in the following scenarios:

  • The write contains null values for all foreign key columns (if MATCH FULL is specified).
  • The write contains null values for at least one foreign key column (if MATCH SIMPLE is specified).
    For more information about composite foreign keys, see the composite foreign key matching section.

Note that allowing null values in either your foreign key or referenced columns can degrade their referential integrity, since any key with a null value is never checked against the referenced table. To avoid this, you can use a NOT NULL constraint on foreign keys when creating your tables.

Note:

A NOT NULL constraint cannot be added to existing tables.

Composite foreign key matching

New in v19.1: By default, composite foreign keys are matched using the MATCH SIMPLE algorithm (which is the same default as Postgres). MATCH FULL is available if specified.

In versions 2.1 and earlier, the only option for composite foreign key matching was an incorrect implementation of MATCH FULL. This allowed null values in the referencing key columns to correspond to null values in the referenced key columns. This was incorrect in two ways:

  • MATCH FULL should not allow mixed null and non-null values. See below for more details on the differences between comparison methods.
  • Null values cannot ever be compared to each other.
    To correct these issues, all composite key matches defined prior to version 19.1 will now use the MATCH SIMPLE comparison method. We have also added the ability to specify both MATCH FULL and MATCH SIMPLE. If you had a composite foreign key constraint and have just upgraded to version 19.1, then please check that MATCH SIMPLE works for your schema and consider replacing that foreign key constraint with a MATCH FULL one.

How it works

For matching purposes, composite foreign keys can be in one of three states:

  • Valid: Keys that can be used for matching foreign key relationships.

  • Invalid: Keys that will not be used for matching (including for any cascading operations).

  • Unacceptable: Keys that cannot be inserted at all (an error is signalled).

MATCH SIMPLE stipulates that:

  • Valid keys may not contain any null values.

  • Invalid keys contain one or more null values.

  • Unacceptable keys do not exist from the point of view of MATCH SIMPLE; all composite keys are acceptable.

MATCH FULL stipulates that:

  • Valid keys may not contain any null values.

  • Invalid keys must have all null values.

  • Unacceptable keys have any combination of both null and non-null values. In other words, MATCH FULL requires that if any column of a composite key is NULL, then all columns of the key must be NULL.

For examples showing how these key matching algorithms work, see Match composite foreign keys with MATCH SIMPLE and MATCH FULL.

Note:

CockroachDB does not support MATCH PARTIAL. For more information, see issue #20305.

Foreign key actions

When you set a foreign key constraint, you can control what happens to the constrained column when the column it's referencing (the foreign key) is deleted or updated.

ParameterDescription
ON DELETE NO ACTIONDefault action. If there are any existing references to the key being deleted, the transaction will fail at the end of the statement. The key can be updated, depending on the ON UPDATE action. Alias: ON DELETE RESTRICT
ON UPDATE NO ACTIONDefault action. If there are any existing references to the key being updated, the transaction will fail at the end of the statement. The key can be deleted, depending on the ON DELETE action. Alias: ON UPDATE RESTRICT
ON DELETE RESTRICT / ON UPDATE RESTRICTRESTRICT and NO ACTION are currently equivalent until options for deferring constraint checking are added. To set an existing foreign key action to RESTRICT, the foreign key constraint must be dropped and recreated.
ON DELETE CASCADE / ON UPDATE CASCADEWhen a referenced foreign key is deleted or updated, all rows referencing that key are deleted or updated, respectively. If there are other alterations to the row, such as a SET NULL or SET DEFAULT, the delete will take precedence. Note that CASCADE does not list objects it drops or updates, so it should be used cautiously.
ON DELETE SET NULL / ON UPDATE SET NULLWhen a referenced foreign key is deleted or updated, respectively, the columns of all rows referencing that key will be set to NULL. The column must allow NULL or this update will fail.
ON DELETE SET DEFAULT / ON UPDATE SET DEFAULTWhen a referenced foreign key is deleted or updated, respectively, the columns of all rows referencing that key are set to the default value for that column. If the default value for the column is null, this will have the same effect as ON DELETE SET NULL or ON UPDATE SET NULL. The default value must still conform with all other constraints, such as UNIQUE.

Performance

Because the foreign key constraint requires per-row checks on two tables, statements involving foreign key or referenced columns can take longer to execute. You're most likely to notice this with operations like bulk inserts into the table with the foreign keys. For bulk inserts into new tables, use the IMPORT statement instead of INSERT.

You can improve the performance of some statements that use foreign keys by also using INTERLEAVE IN PARENT, but there are tradeoffs. For more information about the performance implications of interleaved tables (as well as the limitations), see the Interleave tables section of Performance best practices.

Syntax

Foreign key constraints can be defined at the table level. However, if you only want the constraint to apply to a single column, it can be applied at the column level.

Note:

You can also add the Foreign Key constraint to existing tables through ADD CONSTRAINT.

Column level

CREATETABLEtable_name(column_namecolumn_typeREFERENCESparent_table(ref_column_name)column_constraints,column_deftable_constraints))
ParameterDescription
table_nameThe name of the table you're creating.
column_nameThe name of the foreign key column.
column_typeThe foreign key column's data type.
parent_tableThe name of the table the foreign key references.
ref_column_nameThe name of the column the foreign key references. If you do not include the ref_column_name you want to reference from the parent_table, CockroachDB uses the first column of parent_table's primary key.
column_constraintsAny other column-level constraints you want to apply to this column.
column_defDefinitions for any other columns in the table.
table_constraintsAny table-level constraints you want to apply.

Example

  1. > CREATE TABLE IF NOT EXISTS orders (
  2. id INT PRIMARY KEY,
  3. customer INT NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  4. orderTotal DECIMAL(9,2),
  5. INDEX (customer)
  6. );

Warning:

CASCADE does not list objects it drops or updates, so it should be used cautiously.

Table level

CREATETABLEtable_name(column_def,CONSTRAINTnameFOREIGN KEY(fk_column_name,)REFERENCESparent_table(ref_column_name,)table_constraints)
ParameterDescription
table_nameThe name of the table you're creating.
column_defDefinitions for the table's columns.
nameThe name of the constraint.
fk_column_nameThe name of the foreign key column.
parent_tableThe name of the table the foreign key references.
ref_column_nameThe name of the column the foreign key references. If you do not include the column_name you want to reference from the parent_table, CockroachDB uses the first column of parent_table's primary key.
table_constraintsAny other table-level constraints you want to apply.

Example

  1. CREATE TABLE packages (
  2. customer INT,
  3. "order" INT,
  4. id INT,
  5. address STRING(50),
  6. delivered BOOL,
  7. delivery_date DATE,
  8. PRIMARY KEY (customer, "order", id),
  9. CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
  10. ) INTERLEAVE IN PARENT orders (customer, "order")
  11. ;

Usage examples

Use a foreign key constraint with default actions

In this example, we'll create a table with a foreign key constraint with the default actions (ON UPDATE NO ACTION ON DELETE NO ACTION).

First, create the referenced table:

  1. > CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

Next, create the referencing table:

  1. > CREATE TABLE IF NOT EXISTS orders (
  2. id INT PRIMARY KEY,
  3. customer INT NOT NULL REFERENCES customers (id),
  4. orderTotal DECIMAL(9,2),
  5. INDEX (customer)
  6. );

Let's insert a record into each table:

  1. > INSERT INTO customers VALUES (1001, 'a@co.tld'), (1234, 'info@cockroachlabs.com');
  1. > INSERT INTO orders VALUES (1, 1002, 29.99);
  1. pq: foreign key violation: value [1002] not found in customers@primary [id]

The second record insertion returns an error because the customer 1002 doesn't exist in the referenced table.

Let's insert a record into the referencing table and try to update the referenced table:

  1. > INSERT INTO orders VALUES (1, 1001, 29.99);
  1. > UPDATE customers SET id = 1002 WHERE id = 1001;
  1. pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

The update to the referenced table returns an error because id = 1001 is referenced and the default foreign key action is enabled (ON UPDATE NO ACTION). However, id = 1234 is not referenced and can be updated:

  1. > UPDATE customers SET id = 1111 WHERE id = 1234;
  1. > SELECT * FROM customers;
  1. +------+------------------------+
  2. | id | email |
  3. +------+------------------------+
  4. | 1001 | a@co.tld |
  5. | 1111 | info@cockroachlabs.com |
  6. +------+------------------------+

Now let's try to delete a referenced row:

  1. > DELETE FROM customers WHERE id = 1001;
  1. pq: foreign key violation: value(s) [1001] in columns [id] referenced in table "orders"

Similarly, the deletion returns an error because id = 1001 is referenced and the default foreign key action is enabled (ON DELETE NO ACTION). However, id = 1111 is not referenced and can be deleted:

  1. > DELETE FROM customers WHERE id = 1111;
  1. > SELECT * FROM customers;
  1. +------+----------+
  2. | id | email |
  3. +------+----------+
  4. | 1001 | a@co.tld |
  5. +------+----------+

Use a Foreign Key Constraint with CASCADE

In this example, we'll create a table with a foreign key constraint with the foreign key actions ON UPDATE CASCADE and ON DELETE CASCADE.

First, create the referenced table:

  1. > CREATE TABLE customers_2 (
  2. id INT PRIMARY KEY
  3. );

Then, create the referencing table:

  1. > CREATE TABLE orders_2 (
  2. id INT PRIMARY KEY,
  3. customer_id INT REFERENCES customers_2(id) ON UPDATE CASCADE ON DELETE CASCADE
  4. );

Insert a few records into the referenced table:

  1. > INSERT INTO customers_2 VALUES (1), (2), (3);

Insert some records into the referencing table:

  1. > INSERT INTO orders_2 VALUES (100,1), (101,2), (102,3), (103,1);

Now, let's update an id in the referenced table:

  1. > UPDATE customers_2 SET id = 23 WHERE id = 1;
  1. > SELECT * FROM customers_2;
  1. +----+
  2. | id |
  3. +----+
  4. | 2 |
  5. | 3 |
  6. | 23 |
  7. +----+
  1. > SELECT * FROM orders_2;
  1. +-----+--------------+
  2. | id | customers_id |
  3. +-----+--------------+
  4. | 100 | 23 |
  5. | 101 | 2 |
  6. | 102 | 3 |
  7. | 103 | 23 |
  8. +-----+--------------+

When id = 1 was updated to id = 23 in customers_2, the update propagated to the referencing table orders_2.

Similarly, a deletion will cascade. Let's delete id = 23 from customers_2:

  1. > DELETE FROM customers_2 WHERE id = 23;
  1. > SELECT * FROM customers_2;
  1. +----+
  2. | id |
  3. +----+
  4. | 2 |
  5. | 3 |
  6. +----+

Let's check to make sure the rows in orders_2 where customers_id = 23 were also deleted:

  1. > SELECT * FROM orders_2;
  1. +-----+--------------+
  2. | id | customers_id |
  3. +-----+--------------+
  4. | 101 | 2 |
  5. | 102 | 3 |
  6. +-----+--------------+

Use a Foreign Key Constraint with SET NULL

In this example, we'll create a table with a foreign key constraint with the foreign key actions ON UPDATE SET NULL and ON DELETE SET NULL.

First, create the referenced table:

  1. > CREATE TABLE customers_3 (
  2. id INT PRIMARY KEY
  3. );

Then, create the referencing table:

  1. > CREATE TABLE orders_3 (
  2. id INT PRIMARY KEY,
  3. customer_id INT REFERENCES customers_3(id) ON UPDATE SET NULL ON DELETE SET NULL
  4. );

Insert a few records into the referenced table:

  1. > INSERT INTO customers_3 VALUES (1), (2), (3);

Insert some records into the referencing table:

  1. > INSERT INTO orders_3 VALUES (100,1), (101,2), (102,3), (103,1);
  1. > SELECT * FROM customers_3;
  1. +-----+-------------+
  2. | id | customer_id |
  3. +-----+-------------+
  4. | 100 | 1 |
  5. | 101 | 2 |
  6. | 102 | 3 |
  7. | 103 | 1 |
  8. +-----+-------------+

Now, let's update an id in the referenced table:

  1. > UPDATE customers_3 SET id = 23 WHERE id = 1;
  1. > SELECT * FROM customers_3;
  1. +----+
  2. | id |
  3. +----+
  4. | 2 |
  5. | 3 |
  6. | 23 |
  7. +----+
  1. > SELECT * FROM orders_3;
  1. +-----+-------------+
  2. | id | customer_id |
  3. +-----+-------------+
  4. | 100 | NULL |
  5. | 101 | 2 |
  6. | 102 | 3 |
  7. | 103 | NULL |
  8. +-----+-------------+

When id = 1 was updated to id = 23 in customers_3, the referencing customer_id was set to NULL.

Similarly, a deletion will set the referencing customer_id to NULL. Let's delete id = 2 from customers_3:

  1. > DELETE FROM customers_3 WHERE id = 2;
  1. > SELECT * FROM customers_3;
  1. +----+
  2. | id |
  3. +----+
  4. | 3 |
  5. | 23 |
  6. +----+

Let's check to make sure the row in orders_3 where customers_id = 2 was updated to NULL:

  1. > SELECT * FROM orders_3;
  1. +-----+-------------+
  2. | id | customer_id |
  3. +-----+-------------+
  4. | 100 | NULL |
  5. | 101 | NULL |
  6. | 102 | 3 |
  7. | 103 | NULL |
  8. +-----+-------------+

Use a Foreign Key Constraint with SET DEFAULT

In this example, we'll create a table with a FOREIGN constraint with the foreign key actions ON UPDATE SET DEFAULT and ON DELETE SET DEFAULT.

First, create the referenced table:

  1. > CREATE TABLE customers_4 (
  2. id INT PRIMARY KEY
  3. );

Then, create the referencing table with the DEFAULT value for customer_id set to 9999:

  1. > CREATE TABLE orders_4 (
  2. id INT PRIMARY KEY,
  3. customer_id INT DEFAULT 9999 REFERENCES customers_4(id) ON UPDATE SET DEFAULT ON DELETE SET DEFAULT
  4. );

Insert a few records into the referenced table:

  1. > INSERT INTO customers_4 VALUES (1), (2), (3), (9999);

Insert some records into the referencing table:

  1. > INSERT INTO orders_4 VALUES (100,1), (101,2), (102,3), (103,1);
  1. +-----+-------------+
  2. | id | customer_id |
  3. +-----+-------------+
  4. | 100 | 1 |
  5. | 101 | 2 |
  6. | 102 | 3 |
  7. | 103 | 1 |
  8. +-----+-------------+

Now, let's update an id in the referenced table:

  1. > UPDATE customers_4 SET id = 23 WHERE id = 1;
  1. > SELECT * FROM customers_4;
  1. +------+
  2. | id |
  3. +------+
  4. | 2 |
  5. | 3 |
  6. | 23 |
  7. | 9999 |
  8. +------+
  1. > SELECT * FROM orders_4;
  1. +-----+-------------+
  2. | id | customer_id |
  3. +-----+-------------+
  4. | 100 | 9999 |
  5. | 101 | 2 |
  6. | 102 | 3 |
  7. | 103 | 9999 |
  8. +-----+-------------+

When id = 1 was updated to id = 23 in customers_4, the referencing customer_id was set to DEFAULT (i.e., 9999). You can see this in the first and last rows of orders_4, where id = 100 and the customer_id is now 9999

Similarly, a deletion will set the referencing customer_id to the DEFAULT value. Let's delete id = 2 from customers_4:

  1. > DELETE FROM customers_4 WHERE id = 2;
  1. > SELECT * FROM customers_4;
  1. +------+
  2. | id |
  3. +------+
  4. | 3 |
  5. | 23 |
  6. | 9999 |
  7. +------+

Let's check to make sure the corresponding customer_id value to id = 101, was updated to the DEFAULT value (i.e., 9999) in orders_4:

  1. > SELECT * FROM orders_4;
  1. +-----+-------------+
  2. | id | customer_id |
  3. +-----+-------------+
  4. | 100 | 9999 |
  5. | 101 | 9999 |
  6. | 102 | 3 |
  7. | 103 | 9999 |
  8. +-----+-------------+

Match composite foreign keys with MATCH SIMPLE and MATCH FULL

The examples in this section show how composite foreign key matching works for both the MATCH SIMPLE and MATCH FULL algorithms. For a conceptual overview, see Composite foreign key matching.

First, let's create some tables. parent is a table with a composite key:

  1. > CREATE TABLE parent (x INT, y INT, z INT, UNIQUE (x, y, z));

full_test has a foreign key on parent that uses the MATCH FULL algorithm:

  1. > CREATE TABLE full_test (
  2. x INT,
  3. y INT,
  4. z INT,
  5. FOREIGN KEY (x, y, z) REFERENCES parent (x, y, z) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE
  6. );

simple_test has a foreign key on parent that uses the MATCH SIMPLE algorithm (the default):

  1. > CREATE TABLE simple_test (
  2. x INT,
  3. y INT,
  4. z INT,
  5. FOREIGN KEY (x, y, z) REFERENCES parent (x, y, z) ON DELETE CASCADE ON UPDATE CASCADE
  6. );

Next, we populate parent with some values:

  1. > INSERT
  2. INTO parent
  3. VALUES (1, 1, 1),
  4. (2, 1, 1),
  5. (1, 2, 1),
  6. (1, 1, 2),
  7. (NULL, NULL, NULL),
  8. (1, NULL, NULL),
  9. (NULL, 1, NULL),
  10. (NULL, NULL, 1),
  11. (1, 1, NULL),
  12. (1, NULL, 1),
  13. (NULL, 1, 1);

Now let's look at some INSERT statements to see how the different key matching algorithms work.

MATCH SIMPLE

Inserting values into the table using the MATCH SIMPLE algorithm (described above) gives the following results:

StatementCan insert?Throws error?Notes
INSERT INTO simple_test VALUES (1,1,1)YesNoReferences parent (1,1,1).
INSERT INTO simple_test VALUES (NULL,NULL,NULL)YesNoDoes not reference parent.
INSERT INTO simple_test VALUES (1,NULL,NULL)YesNoDoes not reference parent.
INSERT INTO simple_test VALUES (NULL,1,NULL)YesNoDoes not reference parent.
INSERT INTO simple_test VALUES (NULL,NULL,1)YesNoDoes not reference parent.
INSERT INTO simple_test VALUES (1,1,NULL)YesNoDoes not reference parent.
INSERT INTO simple_test VALUES (1,NULL,1)YesNoDoes not reference parent.
INSERT INTO simple_test VALUES (NULL,1,1)YesNoDoes not reference parent.
INSERT INTO simple_test VALUES (2,2,NULL)YesNoDoes not reference parent.
INSERT INTO simple_test VALUES (2,2,2)NoYesNo parent reference exists.

MATCH FULL

Inserting values into the table using the MATCH FULL algorithm (described above) gives the following results:

StatementCan insert?Throws error?Notes
INSERT INTO full_test VALUES (1,1,1)YesNoReferences parent(1,1,1).
INSERT INTO full_test VALUES (NULL,NULL,NULL)YesNoDoes not reference parent.
INSERT INTO full_test VALUES (1,NULL,NULL)NoYesCan't mix null and non-null values in MATCH FULL.
INSERT INTO full_test VALUES (NULL,1,NULL)NoYesCan't mix null and non-null values in MATCH FULL.
INSERT INTO full_test VALUES (NULL,NULL,1)NoYesCan't mix null and non-null values in MATCH FULL.
INSERT INTO full_test VALUES (1,1,NULL)NoYesCan't mix null and non-null values in MATCH FULL.
INSERT INTO full_test VALUES (1,NULL,1)NoYesCan't mix null and non-null values in MATCH FULL.
INSERT INTO full_test VALUES (NULL,1,1)NoYesCan't mix null and non-null values in MATCH FULL.
INSERT INTO full_test VALUES (2,2,NULL)NoYesCan't mix null and non-null values in MATCH FULL.
INSERT INTO full_test VALUES (2,2,2)NoYesNo parent reference exists.

See also

Was this page helpful?
YesNo