A child table is one side of a relationship between two tables. It holds a foreign key: a column whose values point to primary-key values in a parent table. For example, product is the parent table and review is the child table because each review stores a product_id. section 2.8
The foreign key creates a link and enforces referential integrity. A new child row must reference a valid parent row, and a change or deletion in the parent must not leave orphan rows in the child table. section 2.8 section 2.9
Create a child table in three practical steps:
- Define its own columns, data types, and primary key. section 2.8
- Add the foreign-key column that will store the related parent key. section 2.8
- Declare a named FOREIGN KEY ... REFERENCES constraint pointing to the parent table and primary key. section 2.8
Reviews belong to products
A review row has review_id as its primary key and product_id as its foreign key. The constraint FOREIGN KEY (product_id) REFERENCES product(product_id) allows a review only when that product exists. Trying to insert a review with product_id 3000 when no matching product exists produces an error. section 2.8 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 a complete review child-table definition, including its primary key, product_id foreign key, and named constraint. section 2.8
A child table can be created with its foreign key already defined, or the constraint can be added afterward. When designing optional relationships, a child-table foreign key may allow NULL when a child record can exist before it is associated with a parent record. section 2.8 section 8.8
