CODEWITHSUNDEEP
sqloracle-databaseforeign-keysddl
dor-b
dor-b

Reputation: 75

Cannot create foreign key on oracle

I'm trying to create a foreign key for a table named: Words_in_group, which holds all the words in a froup that the user creates.

I'm trying to refer the words to a table named: Words, which it's P-key is "Word".

Maybe this has something to do with the table "Words", that uses a composite key?

This is the script for creating the foreign key.

alter table "WORDS_IN_GROUP" add constraint
"WORDS_IN_GROUP_FK1" foreign key ("WORD") references "WORDS" ("WORD")
/

Here are the tables SQL:

CREATE TABLE  "WORDS_IN_GROUP"  (   "GROUP_CODE" NUMBER(9,0) NOT NULL ENABLE, 
"WORD" VARCHAR2(45) NOT NULL ENABLE, 
 CONSTRAINT "WORDS_IN_GROUP_PK" PRIMARY KEY ("GROUP_CODE") ENABLE  ) ;ALTER TABLE  "WORDS_IN_GROUP" ADD CONSTRAINT "WORDS_IN_GROUP_FK" FOREIGN KEY ("GROUP_CODE")
  REFERENCES  "GROUP_OF_WORDS" ("GROUP_CODE") ON DELETE CASCADE ENABLE;CREATE OR REPLACE TRIGGER  "BI_WORDS_IN_GROUP"  before insert on "WORDS_IN_GROUP"                for each row  begin    if :NEW."GROUP_CODE" is null then 
select "GROUP_OF_WORDS_SEQ".nextval into :NEW."GROUP_CODE" from dual; end if; end; /ALTER TRIGGER  "BI_WORDS_IN_GROUP" ENABLE;
***///////////

CREATE TABLE  "WORDS"  (    "WORD" VARCHAR2(45) NOT NULL ENABLE, 
"FILE_SN" NUMBER(9,0) NOT NULL ENABLE, 
"FILE_NAME" VARCHAR2(16) NOT NULL ENABLE, 
"POSITION_NUM" NUMBER(5,0) NOT NULL ENABLE, 
"SECTION_NUM" NUMBER(5,0) NOT NULL ENABLE, 
"WORD_SECTION_POSITION" NUMBER(4,0) NOT NULL ENABLE, 
 CONSTRAINT "WORDS_PK" PRIMARY KEY ("WORD", "FILE_SN") ENABLE ) ;

This is the error that I get:

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

Upvotes: 2

Views: 582

Answers (2)

Piyushkumar Kachhadiya
Piyushkumar Kachhadiya

Reputation: 151

You created composite Primary key for the table WORDS. But you just refer only single column in the table WORDS_IN_GROUP. This is not allowed.

You can refer only those column/group of columns which are unique(either enable Primary key/Unique key). If it is composite then you can create composite FK.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311088

A foreign key must always point to a unique key (either explicitly defined as such or defined as a primary key). As you noted, word in words is not necessarily unique - only the combination of word, file_sn is. You could either make word itself unique or add a flie_sn column to the word_in_group table and have a composite foreign key, whichever fits your application logic better.

Upvotes: 1

Related Questions