CODEWITHSUNDEEP
oracle-database
user2332706
user2332706

Reputation: 39

ORA-02270: no matching unique or primary key for this column-list

I have a table, and the structure is 

CREATE TABLE  "COURSE_ACCREDITED" 
("COURSE_ID" VARCHAR2(50) NOT NULL ENABLE, 
"ACCREDITATION_BODY_ID" VARCHAR2(50) NOT NULL ENABLE, 
"DATE_OBTAINED" VARCHAR2(50), 
 PRIMARY KEY ("COURSE_ID", "ACCREDITATION_BODY_ID", "DATE_OBTAINED") ENABLE, 
 CONSTRAINT "COURSE_ACCREDITED_CON" FOREIGN KEY ("COURSE_ID")
  REFERENCES  "COURSE" ("COURSE_ID") ENABLE)

When I add a constrain 

alter table "COURSE_ACCREDITED" add constraint
"COURSE_ACCREDITED_CON2" foreign key ("ACCREDITATION_BODY_ID") references "COURSE_ACCREDITED"    ("ACCREDITATION_BODY_ID")

It appears ORA-02270: no matching unique or primary key for this column-list

What is the problem?

Upvotes: 0

Views: 15757

Answers (1)

sbaker
sbaker

Reputation: 428

You are creating a composite (or compound) key by making this primary:

("COURSE_ID", "ACCREDITATION_BODY_ID", "DATE_OBTAINED")

and then, you try to make ACCREDITATION_BODY_ID a foreign key, referencing to the same column on the same table. I don't know what are you trying to achieve, but anyway, It's not the correct way to do it.

In my opinion, Reason is: ACCREDITATION_BODY_ID must be a primary key in order to be referenced as a foreign key, but that's not the case here. Your table's constraint is a compound key, and you need to reference all the columns in the foreign key statement. (fix me if I'm wrong)

Try reference all the compound key columns in the foreign key statement. That may solve your problem.

By the way, referencing a primary key as a foreign key in the same table didn't make any sense to me (maybe i'm missing something but still..). I'd consider changing the design.

Upvotes: 2

Related Questions