How do you declare a composite FOREIGN KEY with ON DELETE CASCADE in CREATE TABLE?

## Anatomy of the declaration A foreign key declaration has up to four parts, in this order: ```sql [ CONSTRAINT <name> ] FOREIGN KEY ( <child columns> ) REFERENCES <parent table> [ ( <parent columns> ) ] [ MATCH { SIMPLE | PARTIAL | FULL } ] [ ON DELETE <action> ] [ ON UPDATE <action> ] ``` Only `FOREIGN KEY (...) REFERENCES parent` is mandatory. Everything else is optional, and the optional pieces are where most of the interview questions live. A single-column foreign key may also be written inline on the column, with the `FOREIGN KEY` keyword dropped: `order_id INTEGER NOT NULL REFERENCES orders (id)`. A **composite** foreign key has no inline form at all, because inline syntax offers no place to list two child columns. It must be a table constraint. ## The two column lists correspond by position ```sql CREATE TABLE course_section ( dept_code CHAR(4) NOT NULL, course_no INTEGER NOT NULL, section_no INTEGER NOT NULL, room VARCHAR(20), CONSTRAINT pk_course_section PRIMARY KEY (dept_code, course_no, section_no), CONSTRAINT fk_course_section_course FOREIGN KEY (dept_code, course_no) REFERENCES course (dept_code, course_no) ON DELETE CASCADE ON UPDATE NO ACTION ); ``` The engine pairs child column 1 with parent column 1, child column 2 with parent column 2, and so on. Names are irrelevant to the pairing — they happen to match here, but `FOREIGN KEY (dept, num) REFERENCES course (dept_code, course_no)` would be equally valid, and `FOREIGN KEY (course_no, dept_code) REFERENCES course (dept_code, course_no)` would silently compare the wrong things (or, more likely, fail on type mismatch). The lists must be the same length and pairwise comparable in type. ## The referenced column list can be omitted `REFERENCES course` with no column list means "the parent's declared primary key". The columns are resolved when the constraint is created, so a later change to the parent's key does not quietly retarget an existing constraint. Spelling the columns out is still better practice: it documents which key you meant, and the statement fails loudly if the parent's key is not what you assumed. The target has to be a **declared** primary key or unique key on the parent, over exactly those columns. A column list that merely happens to hold distinct values is not a legal target. ## Referential actions are two independent clauses `ON DELETE` fires when a parent row is deleted; `ON UPDATE` fires when a referenced key value in the parent is changed. They are separate clauses, may appear in either order, and each takes exactly one action: - `NO ACTION` - `RESTRICT` - `CASCADE` - `SET NULL` - `SET DEFAULT` The two most common mistakes are assuming that `ON DELETE CASCADE` implies anything about updates — it does not — and assuming that omitting a clause means cascade. Omitting it means `NO ACTION`, the standard's default: the operation is rejected if it would leave a child row pointing at nothing. Two declaration-level requirements follow from the actions you choose. `SET NULL` requires the child columns to be nullable, so it cannot be combined with `NOT NULL` on those columns. `SET DEFAULT` requires the child columns to have defaults, and those default values must themselves exist in the parent. ## Self-references and forward references A foreign key may reference the table being created — an adjacency list is the classic case: ```sql CREATE TABLE employee ( id INTEGER NOT NULL, manager_id INTEGER, CONSTRAINT pk_employee PRIMARY KEY (id), CONSTRAINT fk_employee_manager FOREIGN KEY (manager_id) REFERENCES employee (id) ON DELETE SET NULL ); ``` Note that `manager_id` is deliberately nullable — the root of the hierarchy has no manager, and `ON DELETE SET NULL` would be illegal on a `NOT NULL` column anyway. Referencing a table that does not exist yet is a different matter: the parent must exist when the constraint is created, so either order your `CREATE TABLE` statements parent-first, or create the tables and add the foreign keys afterwards. ## Frequent declaration errors - Writing a composite foreign key inline on one of the two columns — there is no such form. - Column lists of different lengths, or written in mismatched order. - Targeting parent columns that carry no primary key or unique declaration. - `ON DELETE SET NULL` on child columns declared `NOT NULL`. - Expecting `ON UPDATE` behaviour from an `ON DELETE` clause. - Assuming an omitted action clause means cascade rather than `NO ACTION`.