Declare and exercise a native optional InnoDB reference

In an authorized isolated lab only, create fixed database cxdbmslabfkeasy and persistent InnoDB departments/ employees with the definitions in the reference contract: departmentid parent primary key; employeeid child primary key; nullable employee departmentid referencing the parent. Before each copy, clear only these disposable child rows then parent rows, without disabling checks. Copy supplied inputs and return employeeid,department_id. Positive fixtures contain only matching nonmissing references or NULL.

MySQL dialect.

sql
CREATE TABLE department_input (department_id INT PRIMARY KEY); CREATE TABLE employee_input (employee_id INT PRIMARY KEY,department_id INT NULL);
INSERT INTO department_input VALUES (10); INSERT INTO employee_input VALUES (1,10),(2,NULL);

Required ordering: Ascending employee_id.

Edge cases:

• NULL reference is permitted.
• A nonmissing unknown parent is rejected.