Помню несколько лет назад надо было почистить схему, удалив CONSTRAINTы из табличек.
При этом база вела себя как хотела: то сама удаляла индексы и ругалась при их повторном удалении, то оставляла их. Тогда дело решилось простой обработкой exception, сейчас пришла пора разобраться что тут к чему.
При удалении CONSTRAINT возможны следующие варианты:
Вариант 1: ALTER TABLE tbl DROP CONSTRAINT cons – базовый вариант, который все обычно и пишут
Вариант 2: ALTER TABLE tbl DROP CONSTRAINT cons CASCADE – расширеный вариант, который на самом деле удаляет связанные foreign key
Вариант 3: ALTER TABLE tbl DROP CONSTRAINT cons CASCADE DROP INDEX – машина для убийств
Опция cascade управляет только foreign key, которые ссылаются на таблицы
DROP TABLE chi_tst PURGE;
DROP TABLE tst PURGE;
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
INSERT INTO tst VALUES (1);
CREATE TABLE chi_tst(fk NUMBER NOT NULL, CONSTRAINT fk_chi_tst FOREIGN KEY (fk) REFERENCES tst(pk));
INSERT INTO chi_tst VALUES(1);
ALTER TABLE tst DROP CONSTRAINT tst_pk
ORA-02273: this unique/primary key is referenced by some foreign keys
ALTER TABLE tst DROP CONSTRAINT tst_pk CASCADE;
Table altered
Далее мы не будет использовать опцию cascade, т.к. у нас будет одна таблица
Когда нужно добавлять DROP INDEX, а когда индекс удалится сам? Ответ простой: DROP INDEX лучше добавлять всегда, когда хочется гарантировано удалить индекс.
Для того, что бы ответить на вопрос, когда индекс удалится сам, проведем следующий набор тестов:
Тест 1: primary key + атоматически создаваемый (уникальный индекс)
Тест 2: primary key + вручную созданный уникальный индекс
Тест 3: primary key + вручную созданый неуникальный индекс
Для каждого из тестов попробуем удалить индекс без опции (вариант 1) и с опцией (вариант 3)
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
Table created
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME UNIQUENESS
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
Table created
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME UNIQUENESS
как видно, если индекс создается автоматически, то он удаляется в любом случае
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE UNIQUE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME UNIQUENESS
PK_TST UNIQUE
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE UNIQUE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME UNIQUENESS
Как видно, если индекс создавался вручную, то он не удаляется, если не указать DROP INDEX
Теперь сделаем constraint на обычном индексе. Этот вариант пробую из-за на достаточно подробном, но немного ошибочном посте Ричарда Фута
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME UNIQUENESS
PK_TST NONUNIQUE
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME UNIQUENESS
Как видно, если индекс создавался вручную, то он не удаляется, если не указать DROP INDEX
И последний вариант, который узнал из вышеуказанного поста Фута, основанный на DEFFERABLE CONSTRAINT. Для его поддержки Oracle автоматически создает неуникальный индекс
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk) deferrable;
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME UNIQUENESS
TST_PK NONUNIQUE
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk) deferrable;
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME UNIQUENESS
Вывод: без DROP INDEX удаление CONSTRAINT не удаляет даже автоматически создаваемые индексы
Итого
Для того, что бы гарантировано удалить индекс – добавляйте DROP INDEX к команде. В противном случае индекс удаляется только если он был автоматически создан и является уникальным (т.е. в одном случае из четырех)
