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

If you see this error, it simply means that the target table (the table name following the REFERENCES keyword) lacks a constraint, either a primary key or a unique key defined on the columns listed inside the parentheses.

An example will clarify the matter:

create table test1(
test1_id number,
test1_parent_id number,
test1_child_id number,
col1 varchar2 (30),
constraint test1_pk primary key (test1_id)
)
/
create table test2(
test2_id number,
test1_parent_id number,
test1_child_id number,
col2 varchar2(30),
constraint test2_pk primary key (test2_id)
)
/

Now, suppose you want to add a foreign key constraint on TEST2 as follows:

alter table test2 add constraint test2_fk
foreign key (test1_parent_id, test1_child_id)
references test1(test1_parent_id, test1_child_id)
/

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

Indeed there is no unique key (or primary key) on table TEST1 defined for columns test1_parent_id and test1_child_id.

What if we add a unique index?

create unique index test1_uq_idx on test1(test1_parent_id, test1_child_id)
/
alter table test2 add constraint test2_fk
foreign key (test1_parent_id, test1_child_id)
references test1(test1_parent_id, test1_child_id)
/

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

Nope, we must add a unique constraint on TEST1 as well, referencing the unique index we've just created:

alter table test1 add constraint test1_uq
unique (test1_parent_id, test1_child_id)
using index test1_uq_idx
/

and repeat

alter table test2 add constraint test2_fk
foreign key (test1_parent_id, test1_child_id)
references test1(test1_parent_id, test1_child_id)
/

and finally succeed.


Notes:
i created a separate index just to show that it was not sufficient for the purpose, but it can be done at the time of table creation or when the constraint is added at a later time.

create table test2(
test2_id number,
test1_parent_id number,
test1_child_id number,
col2 varchar2(30),
constraint test2_pk primary key (test2_id),
constraint test1_uq unique (test1_parent_id, test1_child_id)
)
/

Creating the indexes separately from the constraints impacts on how Oracle will handle the disabling or dropping of the constraints. For instance, if Oracle created the index automatically, it will also be dropped when you drop or disable the associated constraint, unless you specify specific options like KEEP INDEX (Oracle 9 and above). Also the uniqueness of the index can play a role, because a non-unique index associated with a unique constraint will not be dropped by default (unless you specify DROP INDEX...), whereas a unique index will be.

What's the point of having a non-unique index associated with a unique constraint?
Well, it is a requirement for deferrable constraints.

So, as you see, there is room for making things quite complicated.

You can brush up your knowledge about constraints in the following documents:

• Managing constraints in Oracle 8i
• Managing constraints in Oracle 9i
• Managing constraints in Oracle 10G

Another thing worth mentioning is that column order is not important, so we could have swapped the two columns either in the index or in the constraint specification.
Of course this doesn't mean that you should specify the column order randomly, because column order affects the way the optimizer picks indexes, so be sure to design indexes properly.

See message translations for ORA-02270 and search additional resources.

---

ORA-02270: per questa lista-colonna non vi sono chiavi uniche o primarie corrispondenti
ORA-02270: no hay ninguna clave única o primaria correspondiente para esta lista de columnas
ORA-02270: la llista de columnes no coincideix amb una clau única o primària
ORA-02270: pas de correspondance de clé primaire ou unique pr cette liste de colonne
ORA-02270: kein entsprechender Primär- o. eindeutiger Schlüssel für diese Spaltenliste
ORA-02270: δεν υπάρχει μοναδικό ή πρωτεύον κλειδί που να ταιριάζει για αυτή τη λίστα στηλών
ORA-02270: der er ingen matchende unik eller primær nøgle til denne kolonneliste
ORA-02270: ingen matchande unik eller primär nyckel för kolumnlistan
ORA-02270: det finnes ingen tilsvarende entydig eller primær nøkkel for kolonnelisten
ORA-02270: yksilöivää avainta tai perusavainta ei ole tätä sarakeluetteloa varten
ORA-02270: erre az oszlop-listára nem illeszthető egyedi vagy elsődleges kulcs
ORA-02270: nu exista chei primare sau unice potrivite pentru această listă-coloană
ORA-02270: Geen overeenkomende unieke of primaire sleutel voor deze kolomlijst.
ORA-02270: não há chave exclusiva ou primária compatível para esta lista de colunas
ORA-02270: nenhuma correspondência de chaves exclusivas/primárias para esta lista de colunas
ORA-02270: для этого списка-столбца нет подходящего уникального или первичного ключа
ORA-02270: pro tento seznam sloupců neexistuje odpovídající jednoznačný či primární klíč
ORA-02270: takýto jedinečný alebo primárny kľúč neexistuje v odkazovanej tabuľke
ORA-02270: niezgodność klucza unikatowego lub głównego dla tej listy kolumn
ORA-02270: bu sütun listesine karşılık gelen benzersiz veya birincil anahtar yok
ORA-02270: no matching unique or primary key for this column-list