Oracle Error ORA-02270 – No Matching Unique or Primary Key

foreign keyoracleoracle-11g

I getting an error report of:

Error report -
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

Can I know why?

parent table

    CREATE TABLE STUDENTINFO
    (
      Student_ID VARCHAR2 (10) PRIMARY KEY,
      Full_Name VARCHAR2 (50) NOT NULL,
      Contact_Number NUMBER (15)NOT NULL,
      Address VARCHAR2 (50) NOT NULL,
      Nationality VARCHAR2 (15) NOT NULL,
      IC_PassportNo VARCHAR2 (15) NOT NULL,
      Programme VARCHAR (75) NOT NULL,
      Email_Address VARCHAR2 (50) NOT NULL REFERENCES                    USERNAMEPASSWORD(Username),
      Parents_Number NUMBER (15)NOT NULL,
      Fingerprint_Template clob
    );

child table

    create table bit_2015_sep_cit4114_fyp_G_
    ( 
      Student_ID VARCHAR2 (10) PRIMARY KEY REFERENCES STUDENTINFO(Student_ID),
      Full_Name VARCHAR2 (50) NOT NULL REFERENCES STUDENTINFO(Full_Name),
      Nationality VARCHAR2 (15) NOT NULL REFERENCES STUDENTINFO(Nationality),
      Fingerprint_Template CLOB NOT NULL REFERENCES        STUDENTINFO(Fingerprint_Template),
     "23/10/2015" VARCHAR2 (15) not null, 
   );

I keep nationality and fingerprint as duplicate because it will be time consuming in verifying based on all information stored in studentinfo, therefore by breaking into individual class will be easier and faster. like for the table STUDENTINFO consist 1 million record, in table bit_2015_sep_cit4114_fyp_G_ will only have 40 records. I keep the nationality column because I have 1 more column in my table structure which is visa renewal which calculated based on the value of nationality in the table.

Best Answer

The error has occurred because you are referencing a column in another table which is not unique. The good answers are already given by Lennart and Balazs Papp.

I would like to explain why do we need a unique column in the parent table. As you said you want to keep duplicate values in the column used for the foreign key which is not possible while creating the table. But you can create a reference to an existing table which contains duplicate values.

If you create a primary key with non-unique index and NOVALIDATE option then it is possible. BUT this can lead to confusing results.

Let me explain a situation.

I have created a table with one column ID which has a primary key constraint with a non-unique index.

SQL>CREATE TABLE t1(id NUMBER);
SQL>CREATE INDEX t1_index on t1(id);
SQL>INSERT INTO t1 VALUES(1);
SQL>INSERT INTO t1 VALUES(1);
SQL>COMMIT;
SQL>SELECT id FROM t1;

    ID
----------
     1
     1

SQL>ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id) USING INDEX t1_index NOVALIDATE;

Let's create another table to reference the first table.

SQL>CREATE TABLE t2(id NUMBER, CONSTRAINT t2_fk FOREIGN KEY(id) REFERENCES t1(id));

Generate some records.

SQL>INSERT INTO t2 VALUES(1);
SQL>COMMIT;

Table t2 has a value 1 which is referencing parent table t1 which has a duplicate value of 1. Which one the child table's id will refer to?

In the above scenario, the foreign key works fine but the primary key in table t1 works only for new values.

Conclusion: A foreign key must always refer to a column or columns declared as either PRIMARY KEY or UNIQUE in Oracle.

Related Question