tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities
Share this page:

Oracle/PLSQL: ORA-02270

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

Description

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

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

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, or a primary key does not exist for this table.

Resolution

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

Option #1

This error commonly occurs when you try to create a foreign key that references a table that doesn't have a primary key. To resolve this problem, create a primary key on the referenced table. Then re-execute your command to create the foreign key.

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)
);

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)
);

You would receive the following error message:

Oracle PLSQL

Since there is no primary key on the supplier table, you can not create a foreign key on the products table that references the supplier table.

You can correct this error by adding a primary key to the supplier table as follows and then re-execute the CREATE TABLE statement for the products table:

ALTER TABLE supplier
 ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);