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.