CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11goracle-sqldeveloper
Roman Lopez
Roman Lopez

Reputation: 65

Cannot create table. SQL Error 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

SQL:

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.

Upvotes: 0

Views: 94

Answers (2)

dave
dave

Reputation: 11975

Meets_In is acting as an associative table. Therefore its primary key should include the foreign keys into the tables it is associating.

Try a primary key consisting of: cid, cname, rno and time.

As others have noted, your primary key for courses is (cid, cname), so you also need to include both of these in your foreign key constraint meets_fk1. Or, if possible, ensure that cid only is the primary key on courses.

(I think time may be a reserved word, so perhaps consider renaming it.)

Upvotes: 1

Utsav
Utsav

Reputation: 8093

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)
);

Upvotes: 2

Related Questions