SQL ORA-02256: number of referencing columns must match referenced columns

Question

these are my tables :

CREATE TABLE EMPLOYEE(
    Emp_id number(4),
    Emp_name varchar2(30),
    Emp_gender varchar2(1),
    Status varchar2(30),
    Years_service number(4),
    Primary Key (emp_id)
);

CREATE TABLE ACTIVITY(
    Act_id number(4),
    Description varchar2(30),
    Category_code varchar2(1),
    Primary Key(Act_id)
);

CREATE TABLE ALLOCATION(
    Emp_id number(4) NOT NULL,
    Act_id number(4) NOT NULL,
    Hourly_rate number(5,2) NOT NULL,
    Primary Key (Act_id, Emp_id),
    Foreign Key (Act_id) REFERENCES ACTIVITY,
    Foreign Key (Emp_id) REFERENCES EMPLOYEE,
    CONSTRAINT CK_ALLOCATION_RATE CHECK(Hourly_rate > 0 and Hourly_rate<300) 
);

CREATE TABLE ACTION(
    Week_no number(2) NOT NULL,
    Hrs_worked number(4,1) NOT NULL,
    Act_id number(4) NOT NULL,
    emp_id number(4) NOT NULL,
    Primary Key (Week_no, Act_id, emp_id),
    Foreign Key (Act_id) References Allocation,
    Foreign Key (emp_id) References Allocation
);

Table employee, activity and allocation are created perfectly. but when i try to create table action and referencing the foreign key to table allocation, it says: ORA-02256: number of referencing columns must match referenced columns.

The error message says it all. You must have the same number of columns as in the primary key, i.e. 2. — jarlh, Apr 17 '15 at 14:25
You have to figure what the foreign key is. Must an Action reference to a Act_id/Emp_id combination or not in the allocation - and still make sense! (Besides, I'd name the tables actions, allocations etc, since the store several different rows.) — jarlh, Apr 17 '15 at 14:41

score 1 · Answer 1 · answered Apr 17 '15 at 14:25

1

When using references I think it is a good idea to include the columns in both tables, even if the syntax makes this unnecessary (the default is to the primary key).

You have a composite primary key, so you need a composite key for the foreign reference:

CREATE TABLE ACTION (
    Week_no number(2) NOT NULL,
    Hrs_worked number(4,1) NOT NULL,
    Act_id number(4) NOT NULL,
    emp_id number(4) NOT NULL,
    Primary Key (Week_no, Act_id, emp_id),
    Foreign Key (Act_id, emp_id) References Allocation(Act_id, emp_id)
);

answered Apr 17 '15 at 14:25

Gordon Linoff

1,122,135
50
484
624

I tried that one already. But it returns me with another error – Luke Apr 17 '15 at 14:28
1

@Luke - what error? This works fine with your other table definitions. Have you don't exactly what Gordon showed, or are you still trying to define two foreign keys? – Alex Poole Apr 17 '15 at 15:25

SQL ORA-02256: number of referencing columns must match referenced columns

1 Answers1