CODEWITHSUNDEEP
sqloracle-database
dmbdnr
dmbdnr

Reputation: 334

ORA-00957 duplicate column name error, when trying to reference the same primary key with 3 foreign keys

I'm having problems with creating tables:

CREATE TABLE EMPLOYEE
(
employee_id NUMBER(5) NOT NULL UNIQUE,
position VARCHAR2(100) NOT NULL,
name VARCHAR2(255) NOT NULL,
salary NUMBER(6) NOT NULL

CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);

CREATE TABLE PROJECT
(
project_id NUMBER(5) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
budget NUMBER(6) NOT NULL,
consultant_leader NUMBER(5) NOT NULL,
developer_leader NUMBER(5) NOT NULL,
project_leader NUMBER(5) NOT NULL,

CONSTRAINT project_pk PRIMARY KEY (PROJECT_ID),

CONSTRAINT fk_leader
    FOREIGN KEY (consultant_leader, developer_leader, project_leader)
    REFERENCES EMPLOYEE (employee_id, employee_id, employee_id)
);

In the last section, when I try to reference the employee's table employee_id, I'm getting ORA-00957. I think it's because the 3 different leader type foreign key references the same employee_id, but as far as I know, it should not be a problem. Is the syntax wrong?

Upvotes: 0

Views: 1140

Answers (2)

etsa
etsa

Reputation: 5060

I think you should create three distinct FK: FK_Consultant, FK_developer, FK_projleader

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270443

Your immediate problem is that you need three foreign key relationships, not one with three columns.

But, there is no need to declare a primary key as being unique. So, I would recommend:

CREATE TABLE EMPLOYEE (
    employee_id NUMBER(5) NOT NULL PRIMARY KEY,
    position VARCHAR2(100) NOT NULL,
    name VARCHAR2(255) NOT NULL,
    salary NUMBER(6) NOT NULL
);

CREATE TABLE PROJECT (
    project_id NUMBER(5) NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    budget NUMBER(6) NOT NULL,
    consultant_leader NUMBER(5) NOT NULL,
    developer_leader NUMBER(5) NOT NULL,
    project_leader NUMBER(5) NOT NULL,
    CONSTRAINT fk_leader FOREIGN KEY (consultant_leader)
         REFERENCES EMPLOYEE (employee_id),
    CONSTRAINT fk_leader FOREIGN KEY (developer_leader)
         REFERENCES EMPLOYEE (employee_id),
    CONSTRAINT fk_leader FOREIGN KEY (project_leader)
         REFERENCES EMPLOYEE (employee_id)
);

You don't need to put the PRIMARY KEY constraint in-line, of course. The advantage of declaring it separately is that you can give the constraint a name to your liking.

Upvotes: 1

Related Questions