What Is a Foreign Key and How Is It Used in SQL?

If you’re working with relational databases, foreign keys are one of the first concepts worth understanding clearly. A foreign key in SQL is a column or set of columns in one table that references the primary key of another table. That reference creates a formal relationship between the two tables, and the database enforces it automatically.

The table that holds the foreign key is called the child table. The table being referenced is called the parent table. This parent-child structure is how relational databases model real-world relationships between entities: customers and orders, users and accounts, products and inventory records.

What Is a Foreign Key in SQL?

A foreign key tells the database that the values in one column must correspond to existing values in another table. When you define a foreign key constraint in SQL on a column, the database takes responsibility for maintaining that link, so you don’t have to enforce it in application code.

What Is Referential Integrity, and Why Does It Matter?

Referential integrity is what the foreign key constraint actually enforces. It prevents a row in the child table from referencing a parent row that doesn’t exist.

A practical example: if an orders table has a customer_id column that references the customers table, referential integrity means you cannot insert an order with a customer_id that has no matching record in customers. The database rejects the insert outright.

This matters because data integrity problems compound quickly. An orphaned row, which references a nonexistent parent, corrupts the logical consistency of your schema and causes unexpected results in queries and application logic.

How Is a Foreign Key Different From a Primary Key?

Primary keys and foreign keys serve different purposes, though they work together closely.

A primary key uniquely identifies each row within its own table. No two rows can share the same primary key value, and it cannot be null. A table can have only one primary key.

A foreign key, by contrast, points to a primary key in a different table. Its job is to establish a relationship, not to identify rows within its own table. A single table can have multiple foreign keys, each referencing a different parent table.

One way to think about it: primary keys answer “what is this row?” and foreign keys answer “what does this row belong to?”

How Do You Create a Foreign Key in SQL?

The standard syntax defines the foreign key inline within a CREATE TABLE statement using a REFERENCES clause. Here’s a straightforward example:

CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_date DATE NOT NULL );

Can You Add a Foreign Key to an Existing Table?

Yes. Use ALTER TABLE with ADD CONSTRAINT to add a foreign key after a table already exists:

SQLALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

This is common when evolving a schema after initial deployment.

What Happens During INSERT, UPDATE, and DELETE With a Foreign Key?

This is where referential integrity in SQL becomes tangible. The constraint is checked on every data modification that touches the relationship.

INSERT: A row inserted into the child table must reference a valid parent row. If customer_id 42 doesn’t exist in customers, inserting an order with customer_id = 42 fails immediately.

UPDATE: Updating a foreign key column follows the same validation as an insert. The new value must exist in the parent table.

DELETE: Deleting a parent row fails if any child rows still reference it, unless you’ve configured a referential action. The two most common are:

ON DELETE CASCADE — deleting a parent row automatically deletes all referencing child rows.
ON DELETE SET NULL — deleting a parent row sets the foreign key column to null in all child rows.

Which one you choose depends on the business logic. Cascading deletes are useful when child rows have no meaning without the parent. Setting null is better when the child record should be retained, but the relationship is no longer valid.

How Do Foreign Keys Work in YugabyteDB?

YugabyteDB is a PostgreSQL-compatible, open source distributed SQL database, and it supports foreign key constraints exactly as PostgreSQL does. That means the syntax, referential actions, and constraint behavior work identically in YugabyteDB’s YSQL API without modification.

What makes this notable is that many distributed databases dropped foreign key support to simplify their architectures. Enforcing referential integrity across distributed nodes requires coordinating constraint checks across shards, which is technically hard. YugabyteDB handles this using ACID-compliant transactions, maintaining full referential integrity SQL even across geographically distributed nodes.

For teams running business-critical relational workloads, this means you don’t have to trade data consistency for scale. A foreign key distributed database that actually enforces constraints is a meaningfully different tool than one that asks you to enforce them at the application layer.

Ready to learn more? The YSQL constraints documentation has the full reference for foreign key syntax and options, and you can book a demo today.