Oracle / PLSQL: ORA-02256 Error Message

Learn the cause and how to resolve the ORA-02256 error message in Oracle.

Description

When you encounter an ORA-02256 error, the following error message will appear:

• ORA-02256: number of referencing columns must match referenced columns

Cause

You tried to reference a table using a unique or primary key, but the columns that you listed did not match the primary key.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error can also occur when you try to create a foreign key that references a table, but you've listed different columns in your foreign key that do not match the primary key. To resolve this problem, modify your foreign key to reference the correct column list.

For example, if you had tried to execute the following commands.

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier (supplier_id, supplier_name)
);

You would receive the following error message:

Since the primary key on the supplier table uses only the supplier_id column (and not the supplier_name column), you must redefine your foreign key to reference only the supplier_id as follows:

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier (supplier_id)
);