Foreign Key Constraint

On this page Carat arrow pointing down
Warning:
CockroachDB v1.0 is no longer supported. For more details, see the Release Support Policy.

The Foreign Key constraint specifies that all of a column's values must exactly match existing values from the column it references, enforcing referential integrity.

For example, if you create a foreign key on orders.customer that references customers.id:

  • Each value inserted or updated in orders.customer must exactly match a value in customers.id.
  • Values in customers.id that are referenced by orders.customer cannot be deleted or updated. However, customers.id values that aren't present in orders.customer can be.
Tip:
If you plan to use Foreign Keys in your schema, consider using interleaved tables, which can dramatically improve query performance.

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.
  • 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:
      • 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 constrain 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 foreign keys only accept NULL values in these scenarios:

  • The row you're ultimately referencing—determined by the statement's other values—contains NULL as the value of the referenced column (i.e., NULL is valid from the perspective of referential integrity)
  • The write contains NULL values for all foreign key columns

For example, if you have a Foreign Key constraint on columns (A, B) and try to insert (1, NULL), the write would fail unless the row with the value 1 for (A) contained a NULL value for (B). However, inserting (NULL, NULL) would succeed.

However, allowing NULL values in either your foreign key or referenced columns can degrade their referential integrity. To avoid this, you can use the Not Null constraint on both sets of columns when creating your tables. (The Not Null constraint cannot be added to existing tables.)

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.

We're currently working to improve the performance of these statements, though.

Tip:
You can improve the performance of some statements that use Foreign Keys by also using INTERLEAVE IN PARENT.

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

CREATE TABLE table_name ( column_name column_type REFERENCES parent_table ( ref_column_name ) column_constraints , column_def table_constraints ) )
Parameter Description
table_name The name of the table you're creating.
column_name The name of the foreign key column.
column_type The foreign key column's data type.
parent_table The name of the table the foreign key references.
ref_column_name The 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_constraints Any other column-level constraints you want to apply to this column.
column_def Definitions for any other columns in the table.
table_constraints Any table-level constraints you want to apply.

Example

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

Table Level

CREATE TABLE table_name ( column_def , CONSTRAINT name FOREIGN KEY ( fk_column_name , ) REFERENCES parent_table ( ref_column_name , ) table_constraints )
Parameter Description
table_name The name of the table you're creating.
column_def Definitions for the table's columns.
name The name of the constraint.
fk_column_name The name of the foreign key column.
parent_table The name of the table the foreign key references.
ref_column_name The 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_constraints Any other table-level constraints you want to apply.

Example

CREATE TABLE packages (
    customer INT,
    "order" INT,
    id INT,
    address STRING(50),
    delivered BOOL,
    delivery_date DATE,
    PRIMARY KEY (customer, "order", id),
    CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
    ) INTERLEAVE IN PARENT orders (customer, "order")
  ;

Usage Example

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

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

> INSERT INTO customers VALUES (1001, 'a@co.tld');

> INSERT INTO orders VALUES (1, 1002, 29.99);
pq: foreign key violation: value [1002] not found in customers@primary [id]
> INSERT INTO orders VALUES (1, 1001, 29.99);

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

See Also


Yes No
On this page

Yes No