A parent table is the related table whose primary key is referenced by a foreign key in another table. The other table is the child table. section 2.11 section 2.8
For example, product is the parent table when product_id is its primary key and review stores product_id as a foreign key. A parent table is created like any standalone table; the relationship is defined from the child table. section 2.8
Use the relationship in this order:
- Create the parent table with its primary key, such as product(product_id). section 2.8
- Create the child table and define its foreign-key column, such as review(product_id). section 2.8
- Use CONSTRAINT ... FOREIGN KEY ... REFERENCES ... to link the child column to the parent primary key. section 2.8
- Add child rows only when their foreign-key values already exist in the parent table. section 2.9
Product and review
A review with product_id = 3000 cannot be added if product_id 3000 is absent from product. Likewise, deleting a referenced product can be rejected because it would leave orphaned review 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) );
Inspect the complete review-table definition, including the named constraint that references product(product_id). section 2.8
