CODEWITHSUNDEEP
sqloracle-databaseindexingforeign-keys
Edith Khattar
Edith Khattar

Reputation: 3

Oracle SQL : Cannot create a foreign key based on index of more than one column

I'm not able to create a foreign key based on index of more than one column.

Adding the foreign key returns the error :

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

Below the code :

CREATE TABLE test_ek_uk (NOOPER VARCHAR2(7) NOT NULL
                        ,NUMSEQ NUMBER NOT NULL
                        ,ANY_TEXT VARCHAR2(4000));

CREATE UNIQUE INDEX test_ek_uk_nooper_numseq ON test_ek_uk (NOOPER, NUMSEQ);


CREATE TABLE test_ek_fk (ID NUMBER UNIQUE NOT NULL, NOOPER VARCHAR2(7), NUMSEQ number);

ALTER TABLE test_ek_fk ADD CONSTRAINT test_ek_fk_FK FOREIGN KEY (NOOPER, NUMSEQ) REFERENCES test_ek_uk (NOOPER, NUMSEQ);

Upvotes: 0

Views: 177

Answers (1)

Maxim Borunov
Maxim Borunov

Reputation: 911

A Foreign Key references either a Primary Key constraint or a Unique constraint. And it must be a constraint, a unique index is not enough.

If you already have index then you can create unique constraint based on that index. For your case:

alter table test_ek_uk
  add constraint test_ek_uk_nooper_numseq unique (NOOPER, NUMSEQ)
  using index altest_ek_uk_nooper_numseq;

But if you don't have that index - there is no need to create it explicitly. So, instead of creating the unique index you could create a unique constraint:

alter table test_ek_uk
  add constraint test_ek_uk_nooper_numseq unique (NOOPER, NUMSEQ);

The unique index is created in the background when you create this unique constraint.

Upvotes: 1

Related Questions