Primary key Constraints Enable

SQL> alter table CLJOBB disable constraint CLJOBB;

associated index (CLJOBB) will be dropped automatically.



SQL> alter table CLJOBB enable constraint CLJOBB;
alter table CLJOBB enable constraint CLJOBB
*
ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - primary key violated




Enabling of the PK constraint requires association with index.
If we now try to enable the PK constraint again, it will pick up the first index it found on that column and will get associated with it. In case there is no index to get associated, oracle will create a new index with the name same as that of PK constraint.



CREATE UNIQUE INDEX "LDBO"."CLJOBB" ON "LDBO"."CLJOBB" ("FIRMNUMBER", "NFINANCIALYEAR", "CODE", "EXCHANGE", "BOOKCODE", "DVALIDUPTO", "NPRODUCTCODE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 104857600 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ;



ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

CREATE INDEX "LDBO"."CLJOBB" ON "LDBO"."CLJOBB" ("FIRMNUMBER", "NFINANCIALYEAR", "CODE", "EXCHANGE", "BOOKCODE", "DVALIDUPTO", "NPRODUCTCODE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 104857600 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ;






SQL> alter table CLJOBB enable constraint CLJOBB;
alter table CLJOBB enable constraint CLJOBB
*
ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - primary key violated


SQL> alter table CLJOBB MODIFY CONSTRAINTS CLJOBB ENABLE noVALIDATE;

Table altered.

SQL>


alter table CLJOBB enable VALIDATE primary key;

ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - 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.


-----------Primary Key- Firmnumber,Nfinancialyear,Code,Exchange,Bookcode,Dvalidupto,Nproductcode-----


Select A.Firmnumber, A.Nfinancialyear, A.Code, A.Exchange, A.Bookcode, A.Dvalidupto, A.Nproductcode From Cljobb A
Where Rowid > ( Select Min(Rowid) From Cljobb B
Where A.Firmnumber=B.Firmnumber And A.Nfinancialyear=B.Nfinancialyear And A.Code=B.Code And A.Exchange=B.Exchange
and A.Bookcode=b.Bookcode and a.Dvalidupto=b.Dvalidupto and A.Nproductcode=b.Nproductcode
);