> This page location: Database Constraints > Foreign Key
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL Foreign Key

**Info:** Foreign keys work the same way across any standard PostgreSQL database, so the syntax and behaviors covered here apply wherever you run Postgres. If you're an enterprise looking for managed Postgres built for the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers strong performance, security, and native integration with the Lakehouse. If you're a developer or startup who needs to ship and scale quickly, [Neon](https://neon.com) is the Postgres platform built for that pace.

**Summary**: in this tutorial, you will learn about the PostgreSQL foreign key and how to add foreign keys to tables using foreign key constraints.

## Introduction to PostgreSQL Foreign Key Constraint

In PostgreSQL, a foreign key is a column or a group of columns in a table that uniquely identifies a row in **another table**.

A foreign key establishes a link between the data in two tables by referencing the [primary key](https://neon.com/postgresql/tutorial/postgresql-primary-key) or a [unique constraint](https://neon.com/postgresql/tutorial/postgresql-unique-constraint) of the referenced table.

The table containing a foreign key is referred to as the referencing table or child table. Conversely, the table referenced by a foreign key is known as the referenced table or parent table.

The main purpose of foreign keys is to maintain referential integrity in a relational database, ensuring that relationships between the parent and child tables are valid.

For example, a foreign key prevents the insertion of values that do not have corresponding values in the referenced table.

Additionally, a foreign key maintains consistency by automatically updating or deleting related rows in the child table when changes occur in the parent table.

A table can have multiple foreign keys depending on its relationships with other tables.

To define a foreign key, you can use a foreign key constraint.

## PostgreSQL foreign key constraint syntax

The following illustrates a foreign key constraint syntax:

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

In this syntax:

- First, specify the name for the foreign key constraint after the `CONSTRAINT` keyword. The `CONSTRAINT` clause is optional. If you omit it, PostgreSQL will assign an auto-generated name.
- Second, specify one or more foreign key columns in parentheses after the `FOREIGN KEY` keywords.
- Third, specify the parent table and parent key columns referenced by the foreign key columns in the `REFERENCES` clause.
- Finally, specify the desired delete and update actions in the `ON DELETE` and `ON UPDATE` clauses.

The delete and update actions determine the behaviors when the primary key in the parent table is deleted and updated.

Since the primary key is rarely updated, the `ON UPDATE action` is infrequently used in practice. We'll focus on the `ON DELETE` action.

PostgreSQL supports the following actions:

- SET NULL
- SET DEFAULT
- RESTRICT
- NO ACTION
- CASCADE

## PostgreSQL foreign key constraint examples

The following statements create the `customers` and `contacts` tables:

```sql
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id)
        REFERENCES customers(customer_id)
);
```

In this example, the `customers` table is the parent table and the `contacts` table is the child table.

Each customer has zero or many contacts and each contact belongs to zero or one customer.

The `customer_id` column in the `contacts` table is the foreign key column that references the primary key column with the same name in the `customers` table.

The following foreign key constraint `fk_customer` in the `contacts` table defines the `customer_id` as the foreign key:

```sql
CONSTRAINT fk_customer
   FOREIGN KEY(customer_id)
      REFERENCES customers(customer_id)
```

Because the foreign key constraint does not have the `ON DELETE` and `ON UPDATE` action, they default to `NO ACTION`.

### NO ACTION

The following inserts data into the `customers` and `contacts` tables:

```sql
INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');

INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@example.com'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@example.com'),
      (2,'David Wright','(408)-222-1234','david.wright@example.com');
```

The following statement deletes the customer id 1 from the `customers` table:

```sql
DELETE FROM customers
WHERE customer_id = 1;
```

Because of the `ON DELETE NO ACTION`, PostgreSQL issues a constraint violation because the referencing rows of the customer id 1 still exist in the `contacts` table:

```
ERROR:  update or delete on table "customers" violates foreign key constraint "fk_customer" on table "contacts"
DETAIL:  Key (customer_id)=(1) is still referenced from table "contacts".
SQL state: 23503
```

The `RESTRICT` action is similar to the `NO ACTION`. The difference only arises when you define the foreign key constraint as `DEFERRABLE` with an `INITIALLY DEFERRED` or `INITIALLY IMMEDIATE` mode. We'll discuss more on this in the upcoming tutorial.

### SET NULL

The `SET NULL` automatically sets `NULL` to the foreign key columns in the referencing rows of the child table when the referenced rows in the parent table are deleted.

First, drop the sample tables and re-create them with the foreign key that uses the `SET NULL` action in the `ON DELETE` clause:

```sql
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id)
	  REFERENCES customers(customer_id)
	  ON DELETE SET NULL
);
```

Second, insert data into the `customers` and `contacts` tables:

```sql
INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');

INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@example.com'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@example.com'),
      (2,'David Wright','(408)-222-1234','david.wright@example.com');
```

Third, delete the customer with id 1 from the `customers` table:

```sql
DELETE FROM customers
WHERE customer_id = 1;
```

Because of the `ON DELETE SET NULL` action, the referencing rows in the `contacts` table are set to NULL.

Finally, display the data in the `contacts` table:

```sql
SELECT * FROM contacts;
```

Output:

```
 contact_id | customer_id | contact_name |     phone      |          email
------------+-------------+--------------+----------------+--------------------------
          3 |           2 | David Wright | (408)-222-1234 | david.wright@example.com
          1 |        null | John Doe     | (408)-111-1234 | john.doe@example.com
          2 |        null | Jane Doe     | (408)-111-1235 | jane.doe@example.com
(3 rows)
```

The output indicates that the values of customer id 1 changed to `NULL`.

### CASCADE

The `ON DELETE CASCADE` automatically deletes all the referencing rows in the child table when the referenced rows in the parent table are deleted. In practice, the `ON DELETE CASCADE` is the most commonly used option.

The following statements recreate the sample tables with the delete action of the `fk_customer` changes to `CASCADE`:

```sql
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id)
	  REFERENCES customers(customer_id)
	  ON DELETE CASCADE
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');

INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@example.com'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@example.com'),
      (2,'David Wright','(408)-222-1234','david.wright@example.com');
```

The following statement deletes the customer id 1:

```sql
DELETE FROM customers
WHERE customer_id = 1;
```

Because of the `ON DELETE CASCADE` action, all the referencing rows in the `contacts` table are automatically deleted:

```sql
SELECT * FROM contacts;
```

Output:

```
 contact_id | customer_id | contact_name |     phone      |          email
------------+-------------+--------------+----------------+--------------------------
          3 |           2 | David Wright | (408)-222-1234 | david.wright@example.com
(1 row)
```

### SET DEFAULT

The `ON DELETE SET DEFAULT` sets the default value to the foreign key column of the referencing rows in the child table when the referenced rows from the parent table are deleted.

## Add a foreign key constraint to an existing table

To add a foreign key constraint to the existing table, you use the following form of the [ALTER TABLE](https://neon.com/postgresql/tutorial/postgresql-alter-table) statement:

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

When adding a foreign key constraint with `ON DELETE CASCADE` option to an existing table, you need to follow these steps:

First, drop existing foreign key constraint:

```sql
ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;
```

Second, add a new foreign key constraint with `ON DELETE CASCADE` action:

```sql
ALTER TABLE child_table
ADD CONSTRAINT constraint_fk
FOREIGN KEY (fk_columns)
REFERENCES parent_table(parent_key_columns)
ON DELETE CASCADE;
```

## Summary

- Use foreign keys to ensure the referential integrity and consistency of data between two tables.
- Use the `FOREIGN KEY` constraint to define a foreign key constraint when creating a table.
- Use the `ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY` to add a foreign key constraint to an existing table.

---

## Related docs (Database Constraints)

- [Primary Key](https://neon.com/postgresql/tutorial/primary-key)
- [CHECK Constraint](https://neon.com/postgresql/tutorial/check-constraint)
- [UNIQUE Constraint](https://neon.com/postgresql/tutorial/unique-constraint)
- [NOT NULL Constraint](https://neon.com/postgresql/tutorial/not-null-constraint)
- [DEFAULT Constraint](https://neon.com/postgresql/tutorial/default-value)