6 Replies Latest reply on Aug 18, 2010 7:38 AM by ludovic.sz

    drop primary key constraint

    690743
      hii,,
      i have droppd a primary key constraint & when i try and insert a duplicate
      record it still gives error of Unique key violaion..can anyone please explain me why
      am i not able to insert..?
      CONSTRAINT_NAME                TABLE_NAME                COLNM
      ------------------------------ ------------------------- ---------------
      SYS_C005380                    LOAN_DETAIL               LND_TYPE
      SYS_C005379                    LOAN_DETAIL               LND_LNH_EMP_CODE
      SYS_C005378                    LOAN_DETAIL               LND_LNH_NO
      SYS_C005377                    LOAN_DETAIL               LND_LNH_CODE
      
      *LND_PK                         LOAN_DETAIL               LND_TYPE*
      *LND_PK                         LOAN_DETAIL               LND_LNH_EMP_CODE*
      
      LNDH_FK                        LOAN_DETAIL               LND_LNH_CODE
      LNDH_FK                        LOAN_DETAIL               LND_LNH_EMP_CODE
      LNDH_FK                        LOAN_DETAIL               LND_LNH_NO
      
      9 rows selected.
      
      SQL> ALTER TABLE LOAN_DETAIL
        2  DROP CONSTRAINT LND_PK;
      
      Table altered.
      
      SQL> desc loan_detail
       Name                                                              Null?    Type
       ----------------------------------------------------------------- -------- ------------------------
       LND_LNH_CODE                                                      NOT NULL VARCHAR2(6)
       LND_LNH_NO                                                        NOT NULL NUMBER(6)
       LND_DATE                                                                   DATE
       LND_LNH_EMP_CODE                                                  NOT NULL VARCHAR2(12)
       LND_TYPE                                                          NOT NULL VARCHAR2(6)
      
      select LND_LNH_CODE,LND_LNH_NO,LND_DATE,LND_LNH_EMP_CODE,LND_TYPE
      from loan_detail
      where LND_LNH_NO = 6;
      
      LND_LN    LND_LNH_NO LND_DATE   LND_LNH_EMP_ LND_TY
      ------ ------------- ---------- ------------ ------
      LAP            6.000 23/03/2009 1946         30    
      LAP            6.000 23/03/2009 1946         40    
      
      SQL> insert into LOAN_DETAIL (LND_LNH_CODE,LND_LNH_NO,LND_DATE,LND_LNH_EMP_CODE,LND_TYPE) values
                                          ('LAP',6,TO_DATE('17/08/2010','DD/MM/YYYY'), '1946',30);
      
      insert into LOAN_DETAIL (LND_LNH_CODE,LND_LNH_NO,LND_DATE,LND_LNH_EMP_CODE,LND_TYPE)
      *
      ERROR at line 1:
      ORA-00001: unique constraint (SCOTT.LND_PK) violated
        • 1. Re: drop primary key constraint
          SanjeevL
          Hi,

          You have dropped the primary key. Basically primary key consists of a combination of unique key and not null. Though it has dropped the constraint for primary key the unique key and not null might still be effective. So if you can first check out again what all constraints are still existing after your dropping the PK it would make things clearer. If unique key is still existing then the error will do arise.
          • 2. Re: drop primary key constraint
            IamOk
            Try this and let me know whether it works
            ALTER TABLE test DROP PRIMARY KEY DROP INDEX;
            • 3. Re: drop primary key constraint
              ludovic.sz
              <div class="jive-message-body">
              Hi,

              Did a unique index exist before the primary key ?

              If it is the case Oracle will use the index to enforce the primary key at its creation and won't drop the index when the key is dropped.

              See below (e.g. on Oracle 10.2.0.5) :

              <pre class="jive-pre">
              SQL> -- TABLE CREATION
              SQL> CREATE TABLE loan_detail (lnd_type NUMBER, lnd_lnh_emp_code NUMBER);

              Table created.

              SQL> -- Data load
              SQL> INSERT INTO loan_detail VALUES (1,1);

              +1 row created.+

              SQL> COMMIT;

              Commit complete.

              SQL> -- Unique index creation
              SQL> CREATE UNIQUE INDEX lnd_idx ON loan_detail(lnd_type,lnd_lnh_emp_code);

              Index created.

              SQL> -- Primay key creation : as a unique index already exists
              SQL> -- on these columns Oracle will use the index to enforce the key
              SQL> ALTER TABLE loan_detail ADD CONSTRAINT lnd_pk PRIMARY KEY (lnd_type, lnd_lnh_emp_code);

              Table altered.

              +SQL> -- Data load with duplicate => primary key lnd_pk violated+
              SQL> insert into loan_detail values (1,1);
              insert into loan_detail values (1,1)
              *+
              ERROR at line 1:
              ORA-00001: unique constraint (U8102123.*LND_PK*) violated


              SQL> -- Drop primary key : does not drop the index as it was there before
              SQL> ALTER TABLE loan_detail DROP CONSTRAINT lnd_pk;

              Table altered.

              +SQL> -- Data load with duplicate => unique index lnd_idx violated !+
              SQL> insert into loan_detail values (1,1);
              insert into loan_detail values (1,1)
              *+
              ERROR at line 1:
              ORA-00001: unique constraint (U8102123.*LND_IDX*) violated


              SQL> -- Drop unique index
              SQL> DROP INDEX lnd_idx;

              Index dropped.

              SQL> insert into loan_detail values (1,1);

              +1 row created.+

              SQL> commit;

              Commit complete.

              SQL> select * from loan_detail;

              LND_TYPE LND_LNH_EMP_CODE
              ---------- ----------------
              +1 1+
              +1 1+


              If now we do the same without having an unique index existing before the key creation :

              SQL> -- TABLE CREATION
              SQL> CREATE TABLE loan_detail (lnd_type NUMBER, lnd_lnh_emp_code NUMBER);

              Table created.

              SQL> -- Data load
              SQL> INSERT INTO loan_detail VALUES (1,1);

              +1 row created.+

              SQL> -- As no unique index already exist on the primary key's field Oracle will create one
              SQL> ALTER TABLE loan_detail ADD CONSTRAINT lnd_pk PRIMARY KEY (lnd_type, lnd_lnh_emp_code);

              Table altered.

              SQL> INSERT INTO loan_detail VALUES (1,1);
              INSERT INTO loan_detail VALUES (1,1)
              *+
              ERROR at line 1:
              ORA-00001: unique constraint (U8102123.LND_PK) violated


              SQL> ALTER TABLE loan_detail DROP CONSTRAINT lnd_pk;

              Table altered.

              SQL> insert into loan_detail values (1,1);

              +1 row created.+

              SQL> commit;

              Commit complete.

              SQL> select * from loan_detail;

              LND_TYPE LND_LNH_EMP_CODE
              ---------- ----------------
              +1 1+
              +1 1+

              </pre>

              Kind regards,

              Ludovic
              • 4. Re: drop primary key constraint
                690743
                Hello friend, thanks for your solution..but one thing..
                SQL> -- As no unique index already exist on the primary key's field Oracle will create one
                i know that haven't issued any command FOR CREATING AN INDEX...

                now how do i find whether oracle has created an INDEX OR any other unique constraints
                on my PK (LND_PK).

                As can be noted above from my constraints listing, besides PK, LND_PK,
                i have a FK and NOT NULL constraints created for my LOAN_DETAIL table.

                kindly suggest..
                • 5. Re: drop primary key constraint
                  ©J7
                  SELECT *
                  FROM dba_constraints
                  WHERE table_name = 'table_name' and CONSTRAINT_TYPE='U';
                  /
                  
                  SELECT *
                  FROM dba_cons_columns WHERE CONSTRAINT_NAME='constraint_name';
                  /
                  • 6. Re: drop primary key constraint
                    ludovic.sz
                    Hi,

                    You can check with the following query if a unique indexes was created on some columns (the information will not appear in constraint views but index views):

                    SELECT i.uniqueness,
                    c.index_name,
                    c.table_name,
                    c.column_name
                    FROM user_ind_columns c, user_indexes i
                    WHERE i.index_name = c.index_name
                    AND i.table_name ='LOAN_DETAIL';

                    Kind regards,
                    Ludovic