A foreign key is one or more columns in a child table that refer to a primary key in a parent table. It links related rows and, when enforced as a foreign key constraint, preserves referential integrity: child rows can use only parent key values that exist. section 6.12 section 2.8
Connecting products and reviews
A product row and a review row both use product_id. product.product_id is the primary key; review.product_id is the foreign key. A review with product_id 3 connects to the product whose product_id is 3, such as Cat-Poop Coffee. section 2.2
Create and use a foreign key in a few practical steps:
- Create the parent table normally, with the referenced column as its primary key. section 2.8
- In the child table, define the foreign key column and add a named CONSTRAINT ... FOREIGN KEY (...) REFERENCES parent_table(primary_key) clause. section 2.11 section 2.8
- Alternatively, create both tables first and add the constraint afterward with ALTER TABLE ... ADD CONSTRAINT where supported. section 2.8
- Insert parent rows before child rows that reference them. A child value with no matching parent value produces an RDBMS error. section 2.9
- Do not delete a referenced parent row unless the relationship remains consistent; the constraint prevents orphan child records. section 2.9
-- comment: works for MySQL and MariaDB -- comment: see the code repo for other RDBMS CREATE TABLE review ( review_id INT PRIMARY KEY, product_id INT NOT NULL, review_text TEXT NOT NULL, datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_product_review FOREIGN KEY (product_id) REFERENCES product (product_id) );
This review-table definition makes product_id a required foreign key and names the link to product as fk_product_review. section 2.8
Relationship design helps decide where the key belongs. In a one-to-many relationship, place the foreign key on the many side: many review rows can point to one user row, but putting it on the one side would require extra user rows. section 5.7
A foreign key checks whether a relationship points to a valid parent row; NOT NULL may also be needed when the relationship is mandatory. Without the constraint, a child row can refer to a nonexistent parent row. section 6.12 section 7.3
