Foreign Key

Q: Can a foreign key be NULL?

Yes, unless the column is declared NOT NULL. A NULL foreign key means the row is not linked to any parent, such as an order that has not yet been assigned to a sales representative.

In Turkish: Yabancı Anahtar

Updated September 30, 20262 min read

In short

A foreign key is a column in one database table that refers to the primary key of another table, linking related rows and keeping those references valid.

What is a foreign key?

A foreign key creates a link between two tables. For example, an orders table might have a customer_id column whose values must match an id in the customers table; that column is a foreign key, and it records which customer placed each order. The table holding the foreign key is often called the child table, and the table it points to is the parent table.

The database enforces the link through a rule called referential integrity: it rejects any insert or update that points to a customer who doesn't exist, and it controls what happens when a parent row is deleted. With ON DELETE RESTRICT, the delete is blocked while orders still refer to the customer; with ON DELETE CASCADE, the customer's orders are deleted too; and with ON DELETE SET NULL, the reference is cleared.

Think of the card number written on a library loan slip: the slip doesn't repeat the member's name and address, it just points to the member's record, and the library won't accept a card number that was never issued. Foreign keys are how relational databases model one-to-many relationships, such as one customer with many orders, and many-to-many relationships through a junction table, and they are what JOIN queries usually follow.

A foreign key is not the same as a primary key. The primary key identifies a row in its own table, while a foreign key stores another table's key to refer to it, so unlike a primary key it can repeat and, if allowed, be NULL. Also note that most databases don't automatically index foreign key columns (MySQL's InnoDB engine is an exception), so adding an index on them usually speeds up joins and deletes.

Key takeaways

A foreign key references the primary key, or a unique column, of another table.
It enforces referential integrity: references must point to rows that exist.
ON DELETE rules such as CASCADE or RESTRICT decide what happens when a parent row is removed.
Foreign key values can repeat, which is how one-to-many relationships are modeled.
Index foreign key columns to keep joins and deletes fast.

Example

Linking orders to customers with a foreign keysql

CREATE TABLE customers (
  id   BIGINT PRIMARY KEY,
  name TEXT NOT NULL
);

-- customer_id must match an existing customers.id
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  total       NUMERIC(10, 2) NOT NULL
);

-- Fails with a foreign key violation: there is no customer with id 999
INSERT INTO orders (id, customer_id, total) VALUES (1, 999, 25.00);

Readers ask

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row in its own table and cannot repeat or be NULL. A foreign key lives in another table, stores that primary key value to reference the row, and can repeat across many rows.

Can a foreign key be NULL?

Yes, unless the column is declared NOT NULL. A NULL foreign key means the row is not linked to any parent, such as an order that has not yet been assigned to a sales representative.

What does ON DELETE CASCADE do?

It tells the database to automatically delete child rows when the parent row they reference is deleted. It is convenient but powerful, so use it only where the child data has no meaning without its parent.