13 Replies Latest reply: Sep 18, 2009 3:12 AM by Hemant K Chitale RSS

    Disable unique key constraint

    448225
      Hi,

      Is it possible to disable a unique key constraint?
      I've tried following code:

      ALTER TABLE table_name
      DISABLE CONSTRAINT uk_constraint_name

      which seems to work fine, but when I try to insert or update a record the unique constraint is still on. However in Toad the UK is marked as disabled.

      Do I have to drop the UK and recreate it after my updates?

      Thanks
        • 1. Re: Disable unique key constraint
          Paul M.
          Is it possible to disable a unique key constraint?
          Yes.
          when I try to insert or update a record the unique constraint is still on.
          Does it mean that you get "unique constraint violated" error ?
          Do I have to drop the UK and recreate it after my updates?
          It shouldn't be necessary. But if you need to disable a unique constraint, I have to think that you're going to insert duplicate keys. If so, how will you re-enable the constraint ?
          • 2. Re: Disable unique key constraint
            Anurag Tibrewal
            Hi,

            Not possibel as such.

            Do you have another index/constraint which is unique and is enabled.
            You can query user_constraints table to find all the constraint for that particular table.
            Select constraint_name, CONSTRAINT_TYPE, table_name, status 
              from user_constraints where table_name = 'table_name';
            Regards
            Anurag Tibrewal
            • 3. Re: Disable unique key constraint
              Dom Brooks
              Do you have a unique index policing the constraint?
              • 4. Re: Disable unique key constraint
                448225
                Yes, when I update or insert I still get "unique constraint violated" error.

                I have to disable the UK to update it for some records. If all the updates are finished the field should still be unique but during my update it's possible that a record get the same value as a record that will be updated later in my procedure.

                Regards
                • 5. Re: Disable unique key constraint
                  Anurag Tibrewal
                  Hi,
                  Select constraint_name, CONSTRAINT_TYPE, table_name, status 
                    from user_constraints where table_name = 'table_name';
                  Can you please post the output of above query after you disable your unique constraint?

                  Do you have any triggers?

                  Regards
                  Anurag Tibrewal.
                  • 6. Re: Disable unique key constraint
                    448225
                    Hi,

                    I've executed your code. There is only the PK, my UK and some FK's.
                    In the result the status of the UK is marked as disabled. However still get the unique constraint error. Why doesn't this work?

                    Thanks
                    • 7. Re: Disable unique key constraint
                      Hemant K Chitale
                      That would happen if you had a UNIQUE Index on the table and a Unique Constraint was defined.

                      In this example, I show the error occurring but, for clarity, I show with the Index name being different from the Constraint name. In your case, the Constraint name is likely the same as the Index name !
                      SQL> create table t_1  (col_1 number, col_2 varchar2(5));
                      
                      Table created.
                      
                      SQL> create unique index t_1_u_1 on t_1 (col_1);
                      
                      Index created.
                      
                      SQL> insert into t_1 values (1,'a');
                      
                      1 row created.
                      
                      SQL> insert into t_1 values (2,'b');
                      
                      1 row created.
                      
                      SQL> commit;
                      
                      Commit complete.
                      
                      SQL>  alter table t_1 add constraint t_1_uk unique (col_1);
                      
                      Table altered.
                      
                      SQL> insert into t_1 values (2,'c');
                      insert into t_1 values (2,'c')
                      *
                      ERROR at line 1:
                      ORA-00001: unique constraint (AWRADMIN.T_1_UK) violated
                      
                      
                      SQL> alter table t_1 disable constraint t_1_uk;
                      
                      Table altered.
                      
                      SQL> insert into t_1 values (2,'c');
                      insert into t_1 values (2,'c')
                      *
                      ERROR at line 1:
                      ORA-00001: unique constraint (AWRADMIN.T_1_U_1) violated
                      
                      
                      SQL>
                      In this example, I show the Unique Index and the Unique Key Constraint having the same name. So when you get the ORA-0001 error, you think that it is reporting from the constraint when it is actually from the Index !
                      SQL> create table t_2 (col_1 number, col_2 varchar2(5));
                      
                      Table created.
                      
                      SQL> create unique index t_2_uk on t_2(col_1);
                      
                      Index created.
                      
                      SQL> insert into t_2 values (100,'a');
                      
                      1 row created.
                      
                      SQL> commit;
                      
                      Commit complete.
                      
                      SQL> alter table t_2 add constraint t_2_uk unique (col_1);
                      
                      Table altered.
                      
                      SQL> insert into t_2 values (100,'b');
                      insert into t_2 values (100,'b')
                      *
                      ERROR at line 1:
                      ORA-00001: unique constraint (AWRADMIN.T_2_UK) violated
                      
                      
                      SQL> alter table t_2 disable constraint t_2_uk;
                      
                      Table altered.
                      
                      SQL> insert into t_2 values (100,'b');
                      insert into t_2 values (100,'b')
                      *
                      ERROR at line 1:
                      ORA-00001: unique constraint (AWRADMIN.T_2_UK) violated
                      
                      
                      SQL> select index_name, uniqueness, status from user_indexes where table_name = 'T_2';
                      
                      INDEX_NAME                     UNIQUENES STATUS
                      ------------------------------ --------- --------
                      T_2_UK                         UNIQUE    VALID
                      
                      SQL> drop index t_2_uk;
                      
                      Index dropped.
                      
                      SQL> insert into t_2 values (100,'b');
                      
                      1 row created.
                      
                      SQL>
                      Hemant K Chitale
                      http://hemantoracledba.blogspot.com
                      • 8. Re: Disable unique key constraint
                        448225
                        There are some triggers on the table that are created via Headstart while generating the table API.

                        This is the output of the query:
                        CONSTRAINT_NAME     CONSTRAINT_TYPE     TABLE_NAME     STATUS
                        S715_IOP_PK     P     S715_INV_OPERATIONS     ENABLED
                        S715_IOP_UK1     U     S715_INV_OPERATIONS     DISABLED
                        S715_IOP_INO_FK     R     S715_INV_OPERATIONS     ENABLED
                        S715_IOP_INV_FK     R     S715_INV_OPERATIONS     ENABLED
                        S715_IOP_IOP_FK     R     S715_INV_OPERATIONS     ENABLED
                        S715_IOP_MAI_FK     R     S715_INV_OPERATIONS     ENABLED
                        Regards
                        • 9. Re: Disable unique key constraint
                          Dom Brooks
                          Back to my original question - do you have a unique index on this table?
                          i.e. don't look at user/dba_constraints look at user/dba_indexes.
                          • 10. Re: Disable unique key constraint
                            448225
                            Indeed there is a unique index with the same name.
                            If I drop the unique key, is the unique index also dropped automatic?
                            For example:
                            1) Drop UK (index also automatic dropped???)
                            2) Updates
                            3) Recreate UK (index is recreated)

                            Or do I have to do the following steps:

                            1) Disable the UK
                            2) Drop the unique index
                            3) Updates
                            4) Enable UK
                            5) Recreate unique index

                            Thanks
                            • 11. Re: Disable unique key constraint
                              Anurag Tibrewal
                              Hi,
                              S715_IOP_PK     P     S715_INV_OPERATIONS     ENABLED
                              You have a primary key which has a property of uniqueness. You need to disable this too.

                              Regards
                              Anurag Tibrewal.
                              • 12. Re: Disable unique key constraint
                                Peter Gjelstrup
                                Hi Ken,

                                That depends... On how you created the unique Index, Or rather whether you explicitly created the underlying index.

                                SQL> create table t (x number not null)
                                Table created.
                                SQL> alter table t add constraint implicit_index_uk unique (x)
                                Table altered.
                                SQL> insert into t values (1)
                                1 row created.
                                SQL> insert into t values (1)
                                insert into t values (1)
                                Error at line 11
                                ORA-00001: unique constraint (SCOTT.IMPLICIT_INDEX_UK) violated
                                
                                SQL> commit
                                Commit complete.
                                SQL> alter table t disable constraint implicit_index_uk
                                Table altered.
                                SQL> insert into t values (1)
                                1 row created.
                                SQL> commit
                                Commit complete.
                                SQL> drop table t purge
                                Table dropped.
                                SQL> create table t (x number not null)
                                Table created.
                                SQL> create unique index t_ux on t (x)
                                Index created.
                                SQL> alter table t add constraint explicit_index_uk unique (x)
                                Table altered.
                                SQL> insert into t values (1)
                                1 row created.
                                SQL> insert into t values (1)
                                insert into t values (1)
                                Error at line 32
                                ORA-00001: unique constraint (SCOTT.EXPLICIT_INDEX_UK) violated
                                
                                SQL> commit
                                Commit complete.
                                SQL> alter table t disable constraint explicit_index_uk
                                Table altered.
                                SQL> insert into t values (1)
                                insert into t values (1)
                                Error at line 38
                                ORA-00001: unique constraint (SCOTT.T_UX) violated
                                
                                SQL> commit
                                Commit complete.
                                Regards
                                Peter
                                • 13. Re: Disable unique key constraint
                                  Hemant K Chitale
                                  The behaviour I demonstrated occurs if
                                  a. You have a Pre-Existing UNIQUE Index
                                  b. You add a Unique key Constraint (with the same or a different name, it doesn't matter -- except that the error message shows the index name, being different, if the constraint is disabled)


                                  OTOH, if you define the Unique Key constraint on the table without a pre-existing index, Oracle creates an Index. If you then disable this constraint, Oracle drops the Index.
                                  Therefore, if this is a small table and you can afford to recreate the Index each time, you could follow this method.
                                  SQL> create table t_2 (col_1 number, col_2 varchar2(5));
                                  
                                  Table created.
                                  
                                  SQL> alter table t_2 add constraint t_2_uk unique (col_1);
                                  
                                  Table altered.
                                  
                                  SQL> select index_name, uniqueness, status from user_indexes where table_name = 'T_2';
                                  
                                  INDEX_NAME                     UNIQUENES STATUS
                                  ------------------------------ --------- --------
                                  T_2_UK                         UNIQUE    VALID
                                  
                                  SQL> insert into t_2 values (1,'a');
                                  
                                  1 row created.
                                  
                                  SQL> commit;
                                  
                                  Commit complete.
                                  
                                  SQL> alter table t_2 disable constraint t_2_uk;
                                  
                                  Table altered.
                                  
                                  SQL> insert into t_2 values (1,'b');
                                  
                                  1 row created.
                                  
                                  SQL> select index_name, uniqueness, status from user_indexes where table_name = 'T_2';
                                  
                                  no rows selected
                                  
                                  SQL>SQL> select constraint_name, constraint_type, status from user_constraints where table_name = 'T_2';
                                  
                                  CONSTRAINT_NAME                C STATUS
                                  ------------------------------ - --------
                                  T_2_UK                         U DISABLED
                                  
                                  
                                  SQL> alter table t_2 enable constraint t_2_uk;
                                  alter table t_2 enable constraint t_2_uk
                                  *
                                  ERROR at line 1:
                                  ORA-02299: cannot validate (AWRADMIN.T_2_UK) - duplicate keys found
                                  
                                  
                                  SQL> delete t_2 where col_2 = 'b';
                                  
                                  1 row deleted.
                                  
                                  SQL> alter table t_2 enable constraint t_2_uk;
                                  
                                  Table altered.
                                  
                                  SQL> select index_name, uniqueness, status from user_indexes where table_name = 'T_2';
                                  
                                  INDEX_NAME                     UNIQUENES STATUS
                                  ------------------------------ --------- --------
                                  T_2_UK                         UNIQUE    VALID
                                  
                                  SQL>
                                  Hemant K Chitale