2015-02-25

Удаление CONSTRAINT и индексов

Помню несколько лет назад надо было почистить схему, удалив 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 index
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
------------------------------ ----------

-- индекс создан автоматически, удаляем с DROP INDEX
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 index
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 INDEX
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 index
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 INDEX
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 автоматически создает неуникальный индекс

-- DEFFERABLE, автоматически созданный, неуникальный, без drop index
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

-- DEFFERABLE, автоматически созданный, неуникальный, c DROP INDEX
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 к команде. В противном случае индекс удаляется только если он был автоматически создан и является уникальным (т.е. в одном случае из четырех)

Комментариев нет: