Reputation: 11
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
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