I have created two tables, the parent in this case being "department" and "employee" being child.
CREATE TABLE department ( department_id NUMBER(4) CONSTRAINT department_id_not_null NOT NULL, department_name VARCHAR2(30) CONSTRAINT department_name_not_null NOT NULL, location_id NUMBER(4), manager_id NUMBER(6), CONSTRAINT manager_unique UNIQUE (manager_id), CONSTRAINT department_pk PRIMARY KEY (department_id) ); CREATE TABLE employee ( employee_id NUMBER(6) CONSTRAINT employee_id_not_null NOT NULL, first_name VARCHAR2(20), last_name VARCHAR2(25) CONSTRAINT last_name_not_null NOT NULL, email VARCHAR2(25) CONSTRAINT email_not_null NOT NULL, phone_number VARCHAR2(20), hire_date date CONSTRAINT hire_date_not_null NOT NULL, job_id VARCHAR2(10) CONSTRAINT job_id_not_null NOT NULL, salary NUMBER (8,2), commission_pct NUMBER(2,2), manager_id NUMBER(6), department_id NUMBER (4), CONSTRAINT manager_unique FOREIGN KEY (manager_id) REFERENCES department (manager_id), CONSTRAINT department_fk FOREIGN KEY (department_id) REFERENCES department (department_id), CONSTRAINT employee_pk PRIMARY KEY (employee_id) );
The problem is that whenever I try to create the second table "employee" it gives me this error:
ORA-02270: no matching unique or primary key for this column-list
I haven't been sure about how to reference the unique key in the child table so I also tried using the UNIQUE constraint instead of the FOREIGN KEY constraint but to no avail. The table is created fine without the unique key reference though.