What is a foreign key constraint in SQL? Explain its purpose, how it maintains referential integrity, and provide examples of its implementation in a relational database.
The interviewer expects a clear understanding of foreign key constraints, including their definition, use cases, and potential pitfalls in database design.
A foreign key is a column or a set of columns in one table that uniquely identifies a row of another table. It establishes a link between the two tables.
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Foreign key constraints ensure that the relationship between two tables remains consistent. If a row in the parent table is deleted or updated, corresponding rows in the child table can be restricted or cascaded.
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE;
Cascading actions define how changes in the parent table affect the child table. Options include CASCADE, SET NULL, and NO ACTION.
ON DELETE SET NULL