Roman Lopez Roman Lopez - 2 months ago 14
SQL Question

Cannot create table. SQL Erorr 02270

This is the table I am trying to create. However, I get the error "SQL Error: ORA-02270: no matching unique or primary key for this column-list".

create table Meets_In
(
cid char(20),
rno integer,
time char(20),
CONSTRAINT PRIM_KEY PRIMARY KEY(time),
constraint meets_fk1 foreign key(cid) references COURSES(CID),
constraint meets_fk2 foreign key(rno) references ROOMS(RNO)
);


These are the parent tables:

create table Courses
(
cid char(20),
cname char(20),
credits integer,
constraint CoursesKey Primary Key (cid, cname)
);

CREATE TABLE ROOMS
(
rno INTEGER,
address CHAR(20),
capacity INTEGER,
CONSTRAINT room_key PRIMARY KEY(rno)
);


I don't understand why I am getting this error.

Answer

Cause

The ORA-2270, as the error message suggests, happens when there is no matching unique or primary key for this column-list. This could be because

  • the parent lacks a constraint altogether
  • the parent table's constraint is a compound key and we haven't referenced all the columns in the foreign key statement.

Now in your COURSES table, CID is not a primary key. It is a combination of cid,cname. So for every cid, there can be multiple rows.

Now when you reference cid as foreign key for meets_in, it will not work as it violates the second point as I mentioned above.

Workaround

Add column cname in your meets_in table as well. Then use it like below.

create table Meets_In
(
    cid char(20) not null,
        cname char(20),
    rno integer not null,
    time1 char(20) not null,
    CONSTRAINT PRIM_KEY PRIMARY KEY(time1),
    constraint meets_fk1 foreign key(cid,cname) references COURSES (cid,cname), /*Added cid,cname */
    constraint meets_fk2 foreign key(rno) references ROOMS (RNO)
); 
Comments