What Is a Foreign Key in SQL? Syntax, Rules, and Examples (2026)

A foreign key is a column that holds the id of a row in another table. For example, each order holds the id of the customer who placed it. The database then refuses any insert, update or delete that breaks this link, such as an order for a missing customer.

Foreign key syntax

The table with the foreign key is the child table. The table that the foreign key points at is the parent table. You declare a foreign key in CREATE TABLE, or add it later with ALTER TABLE:

CONSTRAINT constraint_name
  FOREIGN KEY (child_column)
  REFERENCES parent_table (parent_column)
  ON DELETE action
  ON UPDATE action

• CONSTRAINT constraint_name names the foreign key. You need this name to drop the foreign key, and error messages show it.
• FOREIGN KEY (child_column) names the column of the child table that points at the parent table. A foreign key on several columns lists them all here, in the same order as the columns after REFERENCES.
• REFERENCES parent_table (parent_column) names the parent column. The parent column must be a primary key or have a unique constraint.
• ON DELETE and ON UPDATE say what happens to the child rows when the parent row is deleted or the parent's key changes. Both are optional.

On a single column, you can also write REFERENCES parent_table (parent_column) right after the column's type. The database then names the constraint itself.

Create two sample tables, customers and orders, and fill them:

CREATE TABLE customers (
  customer_id int PRIMARY KEY,
  name        varchar(50)
);

CREATE TABLE orders (
  order_id    int PRIMARY KEY,
  customer_id int,
  amount      int,
  CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
);

INSERT INTO customers VALUES
  (1, 'Anna'),
  (2, 'Ben'),
  (3, 'Carla');

INSERT INTO orders VALUES
  (10, 1, 100),
  (11, 1, 300),
  (12, 2, 200);

orders is the child table, and its foreign key orders.customer_id points at customers.customer_id, the primary key of the parent table. The three orders go in, because customers 1 and 2 exist. Carla has no orders, and a parent row may have none.

Run the examples in order. Later queries show the rows that earlier statements changed. The statements were run on PostgreSQL 18, MySQL 9.1 and SQLite 3.50.

On SQLite, turn the checks on first with PRAGMA foreign_keys = ON. What goes wrong shows what happens without it. SQLite also has no ALTER TABLE that adds or drops a constraint, so the DROP CONSTRAINT and ADD CONSTRAINT examples fail there.

Referential integrity

Referential integrity means that every foreign key value points at an existing row. The database protects it in two ways. First, the database refuses an order whose customer doesn't exist:

INSERT INTO orders VALUES (13, 9, 50);

ERROR:  insert or update on table "orders" violates foreign key constraint "fk_orders_customers"
DETAIL:  Key (customer_id)=(9) is not present in table "customers".

No customer has customer_id 9, so PostgreSQL refuses order 13. MySQL refuses the same order with error 1452, "Cannot add or update a child row", and SQLite with "FOREIGN KEY constraint failed".

Second, the database refuses to delete a customer who still has orders:

DELETE FROM customers WHERE customer_id = 1;

ERROR:  update or delete on table "customers" violates foreign key constraint "fk_orders_customers" on table "orders"
DETAIL:  Key (customer_id)=(1) is still referenced from table "orders".

Orders 10 and 11 still point at Anna, so her row stays. MySQL answers with error 1451, "Cannot delete or update a parent row". A DELETE of Carla would succeed, because no order points at her.

Practice this in Lesson 8 of the SQL tutorial: its exercises on a shop database in your browser show which rows a primary key and a foreign key refuse.

NULL in a foreign key

A NULL in a foreign key column means that the row has no parent. For example, you can store an order with no customer.

INSERT INTO orders VALUES (13, NULL, 50);

SELECT * FROM orders;

Order 13 went in with no customer. The database looks up only non-NULL values in customers.

If every order needs a customer, declare customer_id int NOT NULL, and the database refuses the same INSERT. SQL NULL values explains the rest of what NULL does.

DROP CONSTRAINT

ALTER TABLE ... DROP CONSTRAINT removes a foreign key by its name. For example, after you drop fk_orders_customers, orders accepts an order for the missing customer 9.

ALTER TABLE orders DROP CONSTRAINT fk_orders_customers;

INSERT INTO orders VALUES (14, 9, 50);

SELECT * FROM orders;

Order 14 went in, because nothing checks orders.customer_id any more.

MySQL also has a form of its own, ALTER TABLE orders DROP FOREIGN KEY fk_orders_customers. SQL Server uses DROP CONSTRAINT. SQLite drops a foreign key only by creating the table again without the foreign key and copying the rows over.

ADD CONSTRAINT

ALTER TABLE ... ADD CONSTRAINT adds a foreign key to a table that already has rows. For example, you can put fk_orders_customers back on orders.

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers
  FOREIGN KEY (customer_id)
  REFERENCES customers (customer_id);

ERROR:  insert or update on table "orders" violates foreign key constraint "fk_orders_customers"
DETAIL:  Key (customer_id)=(9) is not present in table "customers".

The database first checks the rows already in orders. Order 14 points at the missing customer 9, so PostgreSQL doesn't create the foreign key. MySQL refuses the ALTER TABLE with error 1452.

On a table with thousands of rows, this query lists the rows to fix:

SELECT order_id, customer_id
FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM customers);

Order 13 isn't listed, because a comparison with NULL is never true.

Delete order 14 with DELETE FROM orders WHERE order_id = 14, and run the ADD CONSTRAINT statement again. The database now adds the foreign key, and SELECT * FROM orders returns:

ON DELETE and ON UPDATE

ON DELETE CASCADE deletes the child rows together with their parent row. For example, deleting Anna also deletes her two orders. Without an ON DELETE action, the database refuses that delete, as it did under Referential integrity.

To add CASCADE, drop the foreign key and add it again:

ALTER TABLE orders DROP CONSTRAINT fk_orders_customers;

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers
  FOREIGN KEY (customer_id)
  REFERENCES customers (customer_id)
  ON DELETE CASCADE;

DELETE FROM customers WHERE customer_id = 1;

SELECT * FROM orders;

Deleting Anna also deleted orders 10 and 11, which pointed at her. Order 12 belongs to Ben and order 13 to nobody, so both stay. Check what a cascade will delete before you choose CASCADE: this DELETE named one customer and removed three rows, Anna and her two orders.

Each action does this to the child rows:

With SET NULL in place of CASCADE, the same DELETE would keep orders 10 and 11, with NULL in customer_id. ON UPDATE takes the same actions when the parent's key changes.

In PostgreSQL 18, if a constraint is declared DEFERRABLE, its NO ACTION check can wait until COMMIT. A RESTRICT check can't wait. MySQL treats NO ACTION and RESTRICT alike.

SQL Server offers every action except RESTRICT. MySQL 9.1 accepts ON DELETE SET DEFAULT in CREATE TABLE, but then refuses the parent delete with error 1451. The MySQL manual says that InnoDB, MySQL's default table type, rejects SET DEFAULT.

Self-referencing foreign key

A self-referencing foreign key points at another row of the same table. For example, each customer can point at the customer who referred them.

ALTER TABLE customers
  ADD COLUMN referred_by int REFERENCES customers (customer_id);

UPDATE customers SET referred_by = 2 WHERE customer_id = 3;

SELECT * FROM customers;

Anna is gone: the cascade removed her. Ben referred Carla, so Carla's referred_by holds 2, Ben's customer_id. Ben's NULL means that no customer referred him.

Setting Carla's referred_by to 9 fails, like the order for customer 9.

Foreign key, primary key and unique key

The three constraints differ in what they allow in their column:

In orders, customer_id 1 appeared twice, and order 13 had NULL. A primary key on customer_id would refuse both the duplicate and the NULL. The primary key tutorial explains the parent side.

What goes wrong

No index on the foreign key column

PostgreSQL and SQL Server create no index on the child column. This query lists the indexes of orders in PostgreSQL:

SELECT indexname FROM pg_indexes WHERE tablename = 'orders';

Only the primary key has an index. Add one on customer_id yourself:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Without this index, each delete of a customer makes the database read the whole orders table to find that customer's orders (PostgreSQL 18). MySQL creates the index on its own, named after the constraint. Indexes and foreign keys explains indexing in more detail.

SQLite ignores foreign keys by default

SQLite checks foreign keys only on a connection that has run PRAGMA foreign_keys = ON. For example, on a new connection to the sample tables, SQLite stores an order for customer 9:

INSERT INTO orders VALUES (15, 9, 50);

SELECT * FROM orders;

Run the PRAGMA after you connect, and SQLite refuses the row with "FOREIGN KEY constraint failed". The setting lasts until the connection closes.

MySQL 8.4 ignores REFERENCES after a column

MySQL 8.4 accepts customer_id int REFERENCES customers (customer_id) and creates no foreign key. MySQL 9.0 started to create a foreign key from this form, so on MySQL 9.1 the ALTER TABLE that added referred_by created one. On MySQL 8.4, write the CONSTRAINT ... FOREIGN KEY form from the syntax at the top.

Foreign keys in DbSchema

DbSchema draws each foreign key of a database as a line between two tables. Connect to your database, and DbSchema shows the database's tables on a diagram.

To create a foreign key, drag the child column onto the parent column, such as orders.customer_id onto customers.customer_id. DbSchema opens the New Foreign Key dialog, already filled in. When you press OK, DbSchema creates the foreign key (Foreign Keys).

Double-click the line, and DbSchema opens the Edit Foreign Key dialog. Its On Delete and On Update lists offer the actions that your database supports.

When DbSchema is connected, DbSchema creates the foreign key in the database at once. Offline, the foreign key stays in the model, DbSchema's own copy of your table design. Synchronizing the model with the database, in DbSchema Pro, then creates the foreign key there.

Virtual foreign keys

Some relationships have no constraint in the database. One case is a MySQL table on MyISAM, an older MySQL table type: MySQL parses its foreign keys and ignores them. Another is an old database where nobody declared the relationships.

A virtual foreign key records such a relationship only in the DbSchema model. To create one, drag the columns as above and tick Virtual in the DbSchema dialog.

To keep a virtual foreign key, save the model file in DbSchema Pro. The Query Builder and the Data Explorer follow virtual foreign keys like real ones. Both tools are in Pro.

Download DbSchema at https://dbschema.com/download.html and connect it to your database: the diagram shows each foreign key as a line. Where two tables share a column such as customer_id and no line joins them, nothing checks that relationship yet. The free Community Edition covers connecting, the diagram and the SQL Editor, and saving the model with its virtual foreign keys needs Pro.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download