Referencing a non-PK field in Oracle

January 15, 2023

What is the correct way to reference a non-PK field? I don’t understand why this example doesn’t work (the field c23).

CREATE TABLE tabla1 (
    c11 CHAR(9) PRIMARY KEY,
    c12 VARCHAR2(10),  
    c13 INTEGER,
    CHECK(c13 <= 100)
);

CREATE TABLE tabla2 (
    c21 CHAR(9) PRIMARY KEY,
    c22 VARCHAR2(10),  
    c23 REFERENCES tabla1(c13)
);

>Solution :

You can’t. If you want to create a foreign key constraint, you have to reference a primary or unique key column. Any other column won’t work.

For example:

SQL> CREATE TABLE tabla1 (
  2      c11 CHAR(9)      PRIMARY KEY,
  3      c12 VARCHAR2(10),
  4      c13 INTEGER      UNIQUE,
  5      CHECK(c13 <= 100)
  6  );

Table created.

SQL> CREATE TABLE tabla2 (
  2      c21 CHAR(9) PRIMARY KEY  REFERENCES tabla1 (c11),
  3      c22 VARCHAR2(10),
  4      c23 INTEGER              REFERENCES tabla1(c13)
  5  );

Table created.

SQL>