quabbinの日記: ALTER TABLE DROP CONSTRAINT(FOREIGN KEY)
メモ書き。
必要があってPostgreSQL 7.4.2にて、オンライン状態でのFOREIGN KEYの削除をした。
FOREGIN KEYを削除したい時に可能な操作はいくつかある。
ただ、それらの全てがオンラインでもうまくいくわけでない。
例えば典型的な方法として、中間作業テーブルを新規に作成する方法がある。
SQL文で書くと
BEGIN TRANSACTION;
CREATE TABLE ex2(
col1 CHAR(16) REFERENCES exref
);
INSERT INTO ex2 SELECT * FROM ex;
DROP TABLE ex;
CREATE TABLE ex(
col1 CHAR(16)
);
INSERT INTO ex SELECT * FROM ex2;
DROP TABLE ex2;
COMMIT;
なんて感じだ。
どんなDBエンジンでも(トランザクションが使えるなら)有効な手法なのだが、問題はデータの膨大なコピーが発生するため、膨大なデータ量のテーブルに対しては利用できない。
また、このままではロックがかからないので、他のトランザクションが作業中にデータを挿入しようとして、成功してしまうかもしれない。
特にexからex2に流し込んだ直後で、exをDROP TABLEする直前にデータ挿入が行われると、それが喪失してしまう。
シリアライザブルロックで全てのトランザクションを待ち状態にさせればいいのだが、そうすると計画停止とあまりかわらない。
ではということで、BEGIN TRANSACTION 直後に
LOCK ex;
としてテーブルロックを行うとどうだろうか。
この場合、確かに他のトランザクションからは該当テーブルへの操作のみがCOMMITされるまでロックがかかる。
だが、データベースエンジンにもよるだろうが少なくともPostgreSQLの場合、COMMITされた直後に他のトランザクションでは、
ERROR: relation 928600 deleted while still in use
なんてエラーが出てしまう。
少々のエラーは許してくれるようなシステムなら問題ないかもしれないが、扱っているデータによってはクレームになりかねない作業になってしまうわけだ。
つまり、オンラインではだめな作業といえる。
このような手法しかとれない場合、計画停止を考えたほうがいい。
データのコピー量を減らすため、ALTER TABLE ex RENAME TO ex2;のような手法を使ったとしても、原理的には同じことが起こる。
さて、PostgreSQL 7.4では、ALTER TABLE table_name DROP CONSTRAINTが実装されている。
削除方法について、リファレンスでは
ALTER TABLE [ ONLY ] name [ * ]
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
という記述されていた。
ここでRESTRICTやCASCADEについては詳細を省き、記述上も省く。
今回はFOREGIN KEYを削除したいだけなので、テーブル名とconstraint_nameを探せば削除できるはずだ。
そこで \d exref でpsqlからFOREGIN KEYについている名前を調べる。
すると、
"$1" FOREIGN KEY (col1) REFERENCES exref(col1)
という文字列が出てきた。
どうやらFOREIGN KEYに対してついているconstraint_nameは$1らしい。
そこで
ALTER TABLE ex DROP CONSTRAINT $1;
してみる。
ERROR: syntax error at or near "$1" at character 32;
なんて出てきた。
ならばと$1を括ってみる。
ALTER TABLE ex DROP CONSTRAINT "$1";
成功。
これでオンライン状態であっても大規模なデータ操作は起きず、正しく削除され、オンライン状態でもエラー無く修正された。
ALTER TABLE DROP CONSTRAINT(FOREIGN KEY) More ログイン