Jak smazat na Oraclu unique constraint i s indexem

Vážení diváci, vítejte u dnešní databázové krimi-telenovely!

Epizoda 1

Mějme Oracle databázi 11.2.0.2.0 a v ní tabulku, která má kromě primárního klíče i unique constraint na jinou skupinu sloupců (v praktickém případě, z nějž vychází tento blogpost, byly dva, ale pro demonstraci na minimalistickém příkladě stačí jeden). Pokud založíme constraint příkazem alter table ... add constraint ..., založí se automaticky i index, jak se lze přesvědčit následujícím dotazem:

create table foo (id varchar(26) not null, name varchar(50) not null);
alter table foo add constraint pk_foo primary key (id);
alter table foo add constraint un_foo unique (name); 
select * from all_indexes where table_name = 'FOO' and index_name = 'UN_FOO';
insert into foo values ('id1','name');

Table FOO created.
Table FOO altered.
Table FOO altered.
<1 řádek>
1 row inserted.

V tomto bodě se stane zločin, který se divák dozví až v Epizodě 3.

Epizoda 2

Uplyne nějaký čas a při dalších požadavcích na změnu systému zjistíme, že unique constraint už není unique a databáze se musí zrefaktorovat. Není problém, constraint jednoduše dropneme a jak se lze přesvědčit, zmizel i index a již lze zadávat duplicity:

alter table foo drop constraint un_foo;
select * from all_indexes where table_name = 'FOO' and index_name = 'UN_FOO';
insert into foo values ('id2','name');

Table FOO altered.
<0 řádků>
1 row inserted.

Epizoda 3

Ukáže se že skript z Epizody 2 na některých prostředích nefunguje spolehlivě – po alteru sice constraint zmizí, ale index zůstane viset. Takže jako kdyby tam constraint zůstal. Protože při deploymentu se vyjede jen alter, vše se tváří ok a uživatelé vše poznají, až když dostávají chyby v aplikaci, které neprochází insert.

Pátráním přicházím na to, že ve stavu, do kterého se databáze dostala z nuly po vyvolání skriptu v Epizodě 1, funguje skript správně. Pokud ale po Epizodě 1 databázi zazálohuji v SQL Developeru (menu Tools -> Database copy) a vzápětí obnovím, skript selže a index se nesmaže. Zločinem je tedy zálohování databáze a vrahem není zahradník, ale SQL Developer. Co se konkrétně stalo?

Jak je uvedeno výše, vytvoření constraintu vede automaticky k vytvoření indexu. Toto vytvoření se do zálohovacího skriptu promítne jako explicitní příkaz. Takže obnovu ze zálohy už dělá jiný skript:

create table foo (id varchar(26) not null, name varchar(50) not null);
alter table foo add constraint pk_foo primary key (id);
CREATE UNIQUE INDEX un_foo ON foo (name);
alter table foo add constraint un_foo unique (name);
select * from all_indexes where table_name = 'FOO' and index_name = 'UN_FOO';
insert into foo values ('id1','name');

Posledními dvěma příkazy select a insert lze ověřit, že obnova je k nerozeznání shodná s efektem původního skriptu. Bohužel je tu jeden rozdíl a ten je klíčový – při tvorbě indexu explicitním příkazem se takový index nesmaže pouhým dropnutím constraintu.

Epizoda 4

Tak ho dropnem zvlášť, co je na tom?

alter table foo drop constraint un_foo;
drop index un_foo;

Chyba! Příkaz drop index un_foo; zfailuje v prvním případě, kdy index vznikl implicitně a byl tedy příkazem alter table foo drop constraint un_foo; řádně smazán. Bohužel nic jako drop index un_foo if exists; neexistuje. Slepá větev.

Ok, googlím a najdu toto (pozn. hash v odkazech je podstatná – linkuji vždy konkrétní odpověď ve vlákně). Rada se sice týká dropnutí primary key, ale syntaxe připouští pro unique constraint klauzuli cascade taky. Nicméně dostaneme:

alter table foo drop constraint un_foo cascade;
select * from all_indexes where table_name = 'FOO' and index_name = 'UN_FOO';

Table FOO altered.
<1 řádek>

takže index nezmizel. Opět slepá větev.

Epizoda 5

Kolega radí použít syntax drop primary key ... drop index. To sice není přímo náš případ, neboť nedropujem primary key, ale vzhledem k ukecanosti SQL snaze SQL podobat se přirozenému jazyku se nabízí zkusit to i s constraintem.

alter table foo drop constraint un_foo drop index;
select * from all_indexes where table_name = 'FOO' and index_name = 'UN_FOO';

Table FOO altered.
<0 řádků>

Wow, funguje! Vyzkoušeno na případ jak s implicitně, tak s explicitně založeným indexem.
Šlape dobře, takže odesílám pull request.

Epizoda 6

Kolega, který dělá review pull requestu, se zajímá, kde jsem vzal tu syntax, že ji nikde neviděl. Pomyslím si – stačí ukázat příslušný railroad diagram v dokumentaci a je vymalováno. Nalistuji syntaxi neterminálu drop_constraint_clause v příkazu alter table:

Oops! V případě primárního klíče nebo nepojmenované unique n-tice sloupců je podporováno dropnutí (nebo naopak přání nechtít dropnout) i indexu. Pro drop pojmenovaného constraintu ale nic takového neexistuje. Proč to tedy funguje?

Začínám pátrat. Nacházím zmínku o této syntaxi přímo na AskTomu (sice pro KEEP INDEX, ale to neva, podle syntaktického diagramu je to taky blbě). Dost málo na to si myslet, že to stačí. Není sekvence symbolů alter table ... drop constraint ... drop index jen nějaká jiná – řečí teorie jazyků – derivace neterminálu, kterou v railroad diagramu nevidím? (Např. drop_constraint_clause v Oraclu 12 už je opakující se, takže kdyby připouštěla expanzi pouze na drop index, dvě opakování by náš výraz složily. Smůla. Podobně to nejde rozvinout ani v syntaxi Oracle 8.)

Epizoda 7

Když nezabralo UTFG (Use The Friendly Google), zabere Use The Friendly Stack Overflow. Posílám otázku. Kromě jednoho rychlého ochotného dobrovolníka, který otázce snaživě odebral tag database, ale jinak odpovídal na něco jiného, než jsem se ptal (a po upozornění svoji odpověď briskně smazal), jinak žádná odpověď.

Epizoda 8

Ok, zeptám se přímo na AskTomu. Poprvé kladu dotaz na AskTomu, takže mě čeká registrační proces Oracle účtu s mraky údajů a potvrzovacích checkboxů. Samozřejmě nejprve hledám, zda dotaz již nebyl položen. Dotaz sice nenacházím (myslím k syntaxi – samotný problém mazání je rozebrán víc než dost a záměrně ho do tohoto blogpostu netahám, jako intro lze použít např. toto), ale zato nacházím další výskyty zmíněné syntaxe – přímo od Toma Kyta plus v komentáři u odkazovaného blogu.

Posílám otázku (koukám, jak je rozlámaná, nicméně v preview před odesláním otázka takhle rozlámaná nebyla – Markdown asi do Oraclu ještě nedorazil). Ještě před posláním modeluju situaci na livesql.oracle.com, neboť slibují, že otázkám podpořeným ukázkou se dostane rychlejšího vyřízení. (Zrovna ta site jako na potvoru prochází maintenance pauzou, ale než dopíšu text otázky, je zase up.)

Odpověď od Chrise Saxona je rychlá, ochotná a k věci. Po doplňujícím review a další odpovědi z toho celkově plyne:

• nejprve vtip: když to funguje, musí to být syntakticky správně
• pak domněnka: vypadá to na dokumentační chybu, nicméně nepotvrzeno a může to být i nezamýšlený side efekt (čekal jsem trochu jasnější vyjádření)
• syntaxe je zmíněna i v My Oracle Support (pro mě asi rozhodující argument – sice tam nemám přístup, ale vím, že MOS má pověst alternativní dokumentace a nepředpokládám, že by provedli tak radikální zásah a syntaxi náhle přestali podporovat)
• absolutně bezpečné je údajně pouze to, co je v dokumentaci. Na náš případ by se tedy hodilo použít alter table foo drop unique (name) drop index;

Závěr

Poslední zmíněná syntaxe opravdu funguje i je v souladu s dokumentací. Nicméně vzhledem k ostatním okolnostem jsem zůstal u té původní. Byl to zajímavý výlet do světa Oraclu, na kterém mne překvapila existence nezdokumentované syntaxe i způsob, jakým se lze k takovým informacím dobrat. Problém byl zajímavý i tím, že AskTom byl vhodnějším místem pro jeho řešení než Stack Overflow.