Foreign Key Constraints

A foreign key constrains values in one table so they reference valid rows in another table. It helps a database preserve referential integrity between related records.

A foreign key is a column or set of columns in a child table that references a candidate key, typically a primary key, in a parent table. For example, `orders.customer_id` can reference `customers.id`.

When a foreign key is enforced, an inserted or updated child value must match an allowed parent key, unless the foreign-key column permits `NULL` and the value is `NULL`. This prevents relationships from pointing to nonexistent rows.

Foreign keys also affect deletes and updates of referenced rows. Common actions include `RESTRICT` or `NO ACTION`, which prevent the parent change when dependent rows exist, and `CASCADE`, which propagates the change to related rows. Exact behavior and defaults can vary by database system.

A foreign key can reference multiple columns when the referenced key is composite. The referencing and referenced columns must be compatible according to the database's rules, and the referenced columns generally need an appropriate unique constraint or primary key.

Key points

• A foreign key links child rows to valid parent keys.
• It enforces referential integrity at the database level.
• Child inserts and updates can fail when the referenced parent key does not exist.
• Parent deletes or updates are controlled by the constraint's configured action.
• Foreign keys can use one column or multiple columns for composite relationships.

Interview questions

What happens if you insert a child row whose foreign key value does not exist in the parent table?

The database rejects the operation when the foreign key is enforced and the value is non-null. This protects the relationship from pointing at a nonexistent parent. The application should handle the resulting database error appropriately rather than assuming the insert succeeded.

What are `CASCADE` and `RESTRICT` used for with foreign keys?

They define what happens when a referenced parent row is changed or deleted while child rows depend on it. `CASCADE` can propagate the operation to dependent rows, whereas restrictive behavior can prevent the parent operation when dependencies exist. The exact available actions and default behavior depend on the database system. The choice should match the intended lifecycle of the related data.

What is a foreign key and why is it useful?

A foreign key is a column or group of columns that references a key in another table. It ensures that a child record does not refer to a nonexistent parent record. For example, an order can reference an existing customer through `customer_id`. This is a database-level mechanism for maintaining referential integrity.

Can a foreign key reference multiple columns?

Yes. A foreign key can consist of multiple columns and reference a composite key in the parent table. The participating columns are matched as a group rather than independently. This is useful when the parent relationship is identified by a combination of values.

Why should a backend rely on database foreign keys instead of only checking relationships in Java?

Application checks can reduce avoidable errors, but they do not by themselves provide a concurrency-safe integrity guarantee. Multiple services, scripts, jobs, or transactions may modify the same data, and a check performed before a write can become stale. A database foreign key makes the relationship an enforced invariant at the persistence layer. Java code can still perform validation for clearer errors and better user experience, but it should not be the sole integrity mechanism when the relationship must always hold.