How to resolve ORA-02256 Error Message in Oracle database

Learn the motive and how to unravel the ORA-02256 error message in Oracle.

Description

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

ORA-02256: number of referencing columns have to suit referenced columns

Cause

You tried to reference a desk the usage of a special or predominant key, however the columns that you listed did now not match the most important key.

Resolution

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

Option #1

This error can additionally happen when you try to create a foreign key that references a table, however you’ve listed different columns in your overseas key that do no longer in shape the fundamental key. To unravel this problem, adjust your overseas key to reference the right 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 essential key on the supplier desk makes use of only the supplier_id column (and not the supplier_name column), you ought to redefine your foreign key to reference solely 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)
);