I have created a "department" table where (manager_id) is a foreign key in the second table "employee"
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),
CONSTRAINT manager_unique FOREIGN KEY (manager_id)
REFERENCES department (manager_id),
**
department_id NUMBER (4),
CONSTRAINT department_fk FOREIGN KEY (department_id)
**REFERENCES department (department_id),
CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);**
The problem is whenever I try to create the "employee" table, it gives me this error:
ORA-02270: no matching unique or primary key for this column-list
I tried to reference the unique (manager_id) with "UNIQUE" instead of "FOREIGN KEY" but it gave me a missing right parenthesis error. I hope that someone can help me. Thanks