sql – Why, when I remove a unique constraint, does it continue to work?

Question:

USERDB> ALTER TABLE mw_thirdparty_service DROP CONSTRAINT mt_service_external_unq
[2021-07-05 11:34:33] completed in 112 ms
USERDB> INSERT INTO mw_thirdparty_service(
                     mw_thirdparty_service_id,
                     external_id,
                     gos_no,
                     service_name,
                     supplier_id,
                     mt_ch_limit,
                     accuracy_class,
                     wo_category_id)
                 VALUES(
                     mw_thirdparty_service_seq.nextval,
                     '44008042008733',
                     null,
                     '44008042008733',
                     669,
                     null,
                     null,
                     2)
[2021-07-05 11:34:42] [23000][1] ORA-00001: unique constraint (USERDB.MT_SERVICE_EXTERNAL_UNQ) violated
[2021-07-05 11:34:42] Position: 0
USERDB> select * from ALL_CONSTRAINTS where CONSTRAINT_NAME = 'MT_SERVICE_EXTERNAL_UNQ'
[2021-07-05 11:34:48] 0 rows retrieved in 202 ms (execution: 168 ms, fetching: 34 ms)

I tried to explicitly specify the user / schema when specifying the table, tried to disable the restriction, disable and delete, but it still works.

Searching for constraints on this table with – all_constraints where table_name all constraints except the one you want.
Reloading the DB didn't help either

Answer:

Oracle made a unique index with the same name as constraint, which also needed to be removed

Not really, it was not Oracle that somehow created a unique index, this index already existed before the constraint was created, i.e. was explicitly created.

When creating a unique constraint, a unique index with the same name is always implicitly created. This index will be implicitly removed if the constraint is removed.

Reproducible example ( fiddle 1 ):

create table T (id int)
/
insert into t values (1)
/
1 row inserted.

alter table t add constraint uqt unique (id)
/
insert into t values (1)
/
ORA-00001: unique constraint (DB.UQT) violated

alter table t drop constraint uqt
/
insert into t values (1)
/
1 row inserted.

If the index was created explicitly, then when creating a constraint with the same name, a new index will not be created, but this index will not be deleted when the constraint is removed.

Reproducible example ( fiddle 2 ):

create table T (id int)
/
insert into t values (1)
/
1 row inserted.

create unique index uqt on t (id)
/
alter table t add constraint uqt unique (id)
/
insert into t values (1)
/
ORA-00001: unique constraint (DB.UQT) violated

alter table t drop constraint uqt
/
insert into t values (1)
/
ORA-00001: unique constraint (DB.UQT) violated

In the latter case, you can drop the index in one query along with the drop index constraint ( fiddle 3 ):

alter table t drop constraint uqt drop index
/
insert into t values (1)
/
1 row inserted.
Post Views: 0
Scroll to Top