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



Cause: attempted to validate a primary key with duplicate values or null values.



Action: remove the duplicates and null values before enabling a primary key.

SQL> create table bowie as select rownum id, 'BOWIE' text from dual connect by level <=1000;


Table created.


SQL> create index bowie_idx on bowie(id);


Index created.


SQL> select index_name, uniqueness from user_indexes where index_name = 'BOWIE_IDX';


INDEX_NAME                     UNIQUENES


---------

BOWIE_IDX                      NONUNIQUE


SQL> alter table bowie add constraint bowie_pk primary key (id);


Table altered.


SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.co

nstraint_type from user_ind_columns ic, user_indexes i, user_constraints c where

 ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =

 'BOWIE_IDX';


INDEX_NAME COLUMN_NAME  UNIQUENES CONSTRAINT_NAME                C


------------

------------------------------ -

BOWIE_IDX  ID           NONUNIQUE BOWIE_PK                       P

SQL> CREATE TABLE t (col NUMBER);



Table created.



SQL> ALTER TABLE t

  2  ADD CONSTRAINT pk_t

  3  PRIMARY KEY (col)

  4  INITIALLY DEFERRED DEFERRABLE;



Table altered.



SQL> SELECT index_name, index_type, uniqueness

  2  FROM user_indexes

  3  WHERE table_name = 'T';



INDEX_NAME                     INDEX_TYPE                  UNIQUENES


---------------------------

PK_T                           NORMAL                      NONUNIQUE



SQL>