Home » SQL & PL/SQL » SQL & PL/SQL » Error SQL: ORA-02270: no matching unique or primary key for this column-list (Oracle 11g)
Error SQL: ORA-02270: no matching unique or primary key for this column-list [message #662819] Fri, 12 May 2017 09:44 Go to next message
Cirov
Messages: 13
Registered: May 2017
Junior Member
Hi, i'm getting the error in the subject while trying to create the table with a foreign key constraint:


CREATE TABLE Has (
modelName VARCHAR2(40 CHAR),
manufaturerName VARCHAR2(30 CHAR),
accessoryCode VARCHAR2(15 CHAR),
CONSTRAINT FK_modelName FOREIGN KEY (modelName) REFERENCES MODEL(MODELNAME)
);

The primary key exists in the table MODEL:

SELECT * FROM ALL_CONS_COLUMNS
WHERE TABLE_NAME = 'MODEL';

P17 FK_MANUFACTURER MODEL MANUFACTURERNAME 1
P17 PK_MODEL MODEL MANUFACTURERNAME 2
P17 PK_MODEL MODEL MODELNAME 1


I've created tables with foreign keys before, I'm doing something wrong this time but I can't see what would be the problem.
Re: Error SQL: ORA-02270: no matching unique or primary key for this column-list [message #662821 is a reply to message #662819] Fri, 12 May 2017 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

It seems (but nothing in your post prove it, this is not because a constraint is named "PK..." that it is a PK) that your PK has 2 columns and the FK you try to create only one, so it is correct for Oracle to say there is not PRIMARY or UNIQUE constraint on the table with the lone column you gave.

Re: Error SQL: ORA-02270: no matching unique or primary key for this column-list [message #662825 is a reply to message #662821] Fri, 12 May 2017 10:48 Go to previous messageGo to next message
Cirov
Messages: 13
Registered: May 2017
Junior Member
Thanks Michel, I didn't notice that. I've created now the table with the constraint:

CREATE TABLE Has (
modelName VARCHAR2(40 CHAR),
manufaturerName VARCHAR2(30 CHAR),
accessoryCode VARCHAR2(15 CHAR),
CONSTRAINT FK_modelName FOREIGN KEY (modelName, manufacturerName) REFERENCES MODEL(MODELNAME, MANUFACTURERNAME)
);

[Updated on: Fri, 12 May 2017 10:49]

Report message to a moderator

Re: Error SQL: ORA-02270: no matching unique or primary key for this column-list [message #662826 is a reply to message #662825] Fri, 12 May 2017 10:51 Go to previous message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Previous Topic: Instead of Trigger
Next Topic: Multiple values in one-many relationship
Goto Forum:
  


Current Time: Mon Nov 19 01:35:44 CST 2018