What is a 'FOREIGN KEY'?

Instruction: Define a FOREIGN KEY and its purpose in relational databases.

Context: This question assesses the candidate's understanding of database relationships and the role of foreign keys in enforcing referential integrity.

Updated

Official answer

A foreign key constrains referencing columns to match an eligible key in a referenced table, subject to the declared NULL rules. The referencing values need not be unique: many orders can belong to the same customer. The reference can also be to the same table.

In PostgreSQL:

CREATE TABLE customers (customer_id integer PRIMARY KEY);
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(customer_id)
);

An order for a nonexistent customer is rejected. NOT NULL makes the relationship mandatory; without it, a NULL foreign-key value can be allowed. Referenced keys can be primary or otherwise eligible unique keys, not only primary keys.

Choose delete/update actions deliberately. CASCADE propagates the specified action; it is not a default instruction to remove all related business data. PostgreSQL does not automatically create an index on the referencing columns, and enforcing a relationship has write cost. A foreign key protects the configured relationship, not every rule or the truth of source data.

Related Questions