Kenneth
Kenneth

Reputation: 11

No matching unique or primary key for this column list even after putting the primary key

so I am just starting to learn SQL and can't get a foreign key to reference a primary key.

Here is the table that does not work

CREATE TABLE Classification (
    mvID integer,
    genre varchar (20),
    FOREIGN KEY(mvID) REFERENCES Movie(mvID)
);

Here is the table that it is referencing

CREATE TABLE Movie (
    mvID integer,
    title varchar(40),  
    rating char (2),
    rel_date date,
    length integer,
    studio varchar(40),
    PRIMARY KEY (mvID)
);

Whenever I run it always shows me this message

Error report -

ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.

*Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view

Upvotes: 1

Views: 139

Answers (1)

Tad Harrison
Tad Harrison

Reputation: 1293

Your DDL script works perfectly in Oracle 12c: Check to see if you had a typo or if you have created the tables in the wrong order.

SQL> CREATE TABLE Movie (
    mvID integer,
    title varchar(40),  
    rating char (2),
    rel_date date,
    length integer,
    studio varchar(40),
    PRIMARY KEY (mvID)
);
  2    3    4    5    6    7    8    9  
Table created.

SQL> CREATE TABLE Classification (
    mvID integer,
    genre varchar (20),
    FOREIGN KEY(mvID) REFERENCES Movie(mvID)
);
  2    3    4    5  
Table created.

SQL> drop table Classification;

Table dropped.

SQL> drop table Movie;

Table dropped.

SQL> 

Upvotes: 0

Related Questions