Oracle: ORA-02437 Error Message

ORA-02437 Error Message

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

Description

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

• ORA-02437: cannot validate <name> - primary key violated

Cause

You tried to enable a primary key constraint, but the columns in the primary key either contained NULL values or duplicates.

Resolution

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

Option #1

This error occurs when you try to enable a primary key when there is data in the table, and the columns that make up the primary key contain either NULL values.

For example, if you created the following table:

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

And then the primary key was disabled as follows:

ALTER TABLE supplier;
disable CONSTRAINT supplier_pk;

Then data was inserted into the supplier table with a NULL value for the supplier_id as follows:

INSERT INTO supplier
( supplier_id, supplier_name )
VALUES
(NULL, 'IBM');

And then you tried to enable the primary key:

ALTER TABLE supplier
enable CONSTRAINT supplier_pk;

You would receive the following error message:

You could correct this error by removing the record from the supplier table where the supplier_id column contains a NULL value or you could assign a NOT NULL value to the supplier_id column.

Option #2

This error occurs when you try to enable a primary key when there is data in the table, and the columns that make up the primary key contain duplicates.

For example, if you created the following table:

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

And then the primary key was disabled as follows:

ALTER TABLE supplier
 DISABLE CONSTRAINT supplier_pk;

Then data was inserted into the supplier table to create duplicates in the supplier_id field as follows:

INSERT INTO supplier
( supplier_id, supplier_name )
VALUES
(1, 'IBM');

INSERT INTO supplier
( supplier_id, supplier_name )
VALUES
(1, 'Microsoft');

And then you tried to enable the primary key:

ALTER TABLE supplier
 ENABLE CONSTRAINT supplier_pk;

You would receive the following error message:

You could correct this error by removing the duplicate records from the supplier table.