How do foreign keys work in SQL?

What Are Foreign Keys and Their Purpose? A foreign key in SQL is a column or set of columns in a table that establishes a relationship with the primary key of another table. Its primary purpose is to enforce referential integrity, ensuring that data relationships between tables remain valid. For example, consider an orders table and a customers table. The customer_id in orders can be a foreign key referencing the customer_id (primary key) in customers. This ensures every order is linked to a valid customer. Without foreign keys, you could accidentally create orders for nonexistent customers, leading to inconsistent or “orphaned” data. Foreign keys prevent such issues by requiring that values in the foreign key column(s) exist in the referenced table.

How Foreign Keys Enforce Integrity Foreign keys enforce rules when inserting, updating, or deleting data. For instance, if you try to insert a row into orders with a customer_id that doesn’t exist in customers, the database will reject the operation. Similarly, deleting a customer referenced by an order would violate integrity unless handled properly. SQL allows defining actions like ON DELETE CASCADE to automate these scenarios. For example, ON DELETE CASCADE ensures deleting a customer also deletes all their orders. Other options include SET NULL (set foreign key to NULL if referenced row is deleted) or RESTRICT (block deletion if dependent rows exist). These rules maintain consistency without manual checks.

Implementation and Practical Examples To create a foreign key, you define it during table creation or via ALTER TABLE. Here’s an example:

CREATE TABLE customers (
 customer_id INT PRIMARY KEY,
 name VARCHAR(50)
);

CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 customer_id INT,
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
 ON DELETE CASCADE
);

This links orders.customer_id to customers.customer_id and cascades deletions. Foreign keys can also reference unique constraints (not just primary keys) and involve composite keys (e.g., referencing multiple columns). Adding a foreign key after table creation uses ALTER TABLE orders ADD CONSTRAINT .... Indexing foreign key columns is recommended to speed up join operations and constraint checks. By clearly defining relationships, foreign keys simplify query logic and reduce errors in applications relying on interconnected data.