CODEWITHSUNDEEP
sqloracle-databaseconstraintsunique-constraint
xcoder
xcoder

Reputation: 1436

Dropping an unique constraint does not work

I tried to drop an unique constraint in my db with:

ALTER TABLE MyDbAdmin.myTable
DROP UNIQUE (myCol);

The console said the drop worked but when I try to insert the record with duplicate myCol, ORA-00001: unique constraint error was returned.

I tried looking at the table's constraint page, the unique constraint has indeed gone. Also, if I run the same SQL to drop the constraint again it returned with ORA-02442: Cannot drop nonexistent unique key.

The above queries were run using an account myDbUser, is this the cause of the above weird behaviour?

Upvotes: 3

Views: 2051

Answers (2)

Martin Preiss
Martin Preiss

Reputation: 396

maybe your unique index was created before the constraint was created:

create table t(col1 number);
create unique index t_idx on t(col1);
alter table t add constraint t_unique unique(col1);
insert into t values(1);
--> 1 row created
insert into t values(1);
--> ORA-00001: unique constraint (TEST.T_UNIQUE) violated
alter table t drop unique (col1);
insert into t values(1);
--> ORA-00001: unique constraint (TEST.T_IDX) violated

Though the unique index T_IDX does not occur in user_constraints, it is shown in the error message.

If the index is created internally as part of the "alter table ... add constraint" then you can insert duplicates after the removal of the constraint, since the index supporting the constraint is removed together with the constraint. So without the "create unique index" the code works as expected:

create table t(col1 number);
-- create unique index t_idx on t(col1);
alter table t add constraint t_unique unique(col1);
insert into t values(1);
--> 1 row created
insert into t values(1);
--> ORA-00001: unique constraint (TEST.T_UNIQUE) violated
alter table t drop unique (col1);
insert into t values(1);
--> 1 row created

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142753

It might be useful if you pasted the whole erroring line. Why? We'd see the unique constraint name, and that might be a key to your problem.

Here's what I think: there's a composite unique index that includes the myCol column.

SQL> create table test (mycol number, id number);

Table created.

SQL> alter table test add constraint ukt unique (mycol);

Table altered.

SQL> create unique index i1t on test (mycol, id);

Index created.

SQL>

Testing:

SQL> -- this is OK
SQL> insert into test values (1, 1);

1 row created.

SQL> -- but, this will fail
SQL> insert into test values (1, 1);
insert into test values (1, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UKT) violated


SQL>

UKT constraint was violated, so - let's drop it and try again:

SQL> alter table test drop unique (mycol);

Table altered.

SQL> insert into test values (1, 1);
insert into test values (1, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I1T) violated


SQL>

See which constraint was now violated? I1T.

Once you figure out which constraint was violated, try to find some more info with one of the following:

SQL> select column_name from user_cons_columns where constraint_name = 'I1T';

no rows selected

SQL> select column_name from user_ind_columns where index_name = 'I1T';

COLUMN_NAME
-----------------------------------------------------------------------------
MYCOL
ID

SQL>

Upvotes: 1

Related Questions