Oracle/PLSQL: ORA-02437 Error

Error Message

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

Cause of Error

You tried to 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.