Referential integrity keeps related tables consistent. A foreign key in a child table must point to a valid primary-key value in its parent table, so a review cannot refer to a product or user that does not exist. SQL enforces this rule with a foreign key constraint. section 2.8 section 6.12
Use foreign keys to protect a relationship in three steps:
- Identify the parent table and its primary key, then put the matching foreign key column in the child table. section 6.12
- Declare FOREIGN KEY ... REFERENCES ... when creating the child table, and name the constraint so it is easier to modify later. section 2.8
- Decide what should happen when a referenced parent row changes or is deleted: prevent the operation by default, or use a referential action such as CASCADE or SET NULL. section 6.13
Reviews must belong to real products
In a product and review database, review.product_id references product.product_id. Trying to insert a review with product_id 3000 when no matching product exists produces a foreign-key error. Deleting a product that still has reviews is also stopped by default, preventing orphan review rows. section 2.9
For a mandatory relationship, combine the foreign key with NOT NULL. This prevents both an invalid reference and a missing reference; for example, a review that must be associated with a user needs a non-null user foreign key. section 6.12
-- comment: works for MySQL, MariaDB and PostgreSQL -- comment: see the code repo for other RDBMS CREATE TABLE payment_method ( payment_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, card_number CHAR(16) NOT NULL, expiry_date CHAR(4) NOT NULL, email VARCHAR(320) NOT NULL, CONSTRAINT fk_payment_method_user FOREIGN KEY (email) REFERENCES user (email) ON DELETE CASCADE ON UPDATE CASCADE );
This definition links a payment method to a user and uses ON DELETE CASCADE and ON UPDATE CASCADE to keep matching child rows synchronized. section 6.13
