How to reference a UNIQUE KEY CONSTRAINT in parent table as a foreign key in child table?

Question

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.

Answer 1

Thanks to ypercube,

I had to drop the parent table "department", recreate it and then use ALTER TABLE employee ADD CONSTRAINT fk_manager_unique FOREIGN KEY (manager_id) REFERENCES department (manager_id); everything worked just fine and the foreign key reference to the unique manager_id key were finally created. Note that I had to change the name of the constraint "manager_unique" in the second table because of duplication issues.

Thanks again!