Foreign keys

A foreign key represents one or more columns in a table referencing the following:

• A primary key in another table.
• A unique index or columns restricted with a unique constraint in another table.

Tables can have multiple foreign keys.

You use a foreign key constraint to maintain the referential integrity of data between two tables: values in columns in one table equal the values in columns in another table.

Syntax

Define the foreign key constraint using the following syntax:

[CONSTRAINT fk_name]
  FOREIGN KEY(fk_columns)
    REFERENCES parent_table(parent_key_columns)
    [ON DELETE delete_action]
    [ON UPDATE update_action]

Defining the CONSTRAINT clause and naming the foreign key is optional. If you omit it, an auto-generated name is provided by YSQL. The REFERENCES clause specifies the parent table and its columns referenced by the fk_columns. Defining actions is also optional; if defined, they determine the behaviors when the primary key in the parent table is deleted or updated. YSQL allows you to perform the following actions:

• SET NULL - when the referenced rows in the parent table are deleted or updated, foreign key columns in the referencing rows of the child table are automatically set to NULL.
• SET DEFAULT - when the referenced rows of the parent table are deleted or updated, the default value is set to the foreign key column of the referencing rows in the child table.
• RESTRICT - when the referenced rows in the parent table are deleted or updated, deletion of a referenced row is prevented.
• CASCADE - when the referenced rows in the parent table are deleted or updated, the referencing rows in the child table are deleted or updated.
• NO ACTION (default) - when the referenced rows in the parent table are deleted or updated, no action is taken.

Examples

The following example creates two tables:

CREATE TABLE employees(
  employee_no integer GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL,
  department text,
  PRIMARY KEY(employee_no)
);

CREATE TABLE contacts(
  contact_id integer GENERATED ALWAYS AS IDENTITY,
  employee_no integer,
  contact_name text NOT NULL,
  phone integer,
  email text,
  PRIMARY KEY(contact_id),
  CONSTRAINT fk_employee
    FOREIGN KEY(employee_no)
      REFERENCES employees(employee_no)
);

The parent table is employees and the child table is contacts. Each employee has any number of contacts, and each contact belongs to no more than one employee. The employee_no column in the contacts table is the foreign key column that references the primary key column with the same name in the employees table. The fk_employee foreign key constraint in the contacts table defines the employee_no as the foreign key. By default, NO ACTION is applied because fk_employee is not associated with any action.

The following example shows how to create the same contacts table with a CASCADE action ON DELETE:

CREATE TABLE contacts(
  contact_id integer GENERATED ALWAYS AS IDENTITY,
  employee_no integer,
  contact_name text NOT NULL,
  phone integer,
  email text,
  PRIMARY KEY(contact_id),
  CONSTRAINT fk_employee
    FOREIGN KEY(employee_no)
      REFERENCES employees(employee_no)
      ON DELETE CASCADE
);

Use ALTER TABLE to add or drop a Foreign Key Constraint

You can add a foreign key constraint to an existing table by using the ALTER TABLE statement, using the following syntax:

ALTER TABLE child_table
  ADD CONSTRAINT constraint_name
    FOREIGN KEY (fk_columns)
      REFERENCES parent_table (parent_key_columns);

Before altering a table with a foreign key constraint, you need to remove the existing foreign key constraint, as per the following syntax:

ALTER TABLE child_table
  DROP CONSTRAINT constraint_fkey;

The next step is to add a new foreign key constraint, possibly including an action, as demonstrated by the following syntax:

ALTER TABLE child_table
  ADD CONSTRAINT constraint_fk
    FOREIGN KEY (fk_columns)
      REFERENCES parent_table(parent_key_columns)
      [ON DELETE action];

Learn more

• Table with Foreign Key
• Foreign Keys in PostgreSQL documentation
• Relational Data Modeling with Foreign Keys in a Distributed SQL Database
• Other Constraints