twitter rss
CONSTRAINT句に'IF NOT EXISTS'条件を加える
Oct 22, 2017
One minute read

DBのテーブルにCONSTRAINT句(制約)を定義する際にIF NOT EXISTS条件を書けないのでマイグレーションファイルの作成でいつもつまづく。

例えば、tickets.user_idとusers.idを関連付けたい場合に以下のようなDDLを書く。

ALTER TABLE tickets ADD CONSTRAINT tickets_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

しかしマイグレーションを実行した際に、すでにその環境で定義済みだった場合にAlready existsのようなエラーが返され萎える。ローカル環境であればDDLをコメントアウトしてマイグレーションをスルーしてしまえばいいのだが、本番環境であればそうはいかない。(途中からマイグレーション導入することもあるので)

DDLにIF NOT EXISTSを付けてCONSTRAINTが存在しない場合のみ定義してくれたらいいのだが、この書き方だとSyntax errorが起きる。

-- この書き方だとSyntax errorが起きる
ALTER TABLE tickets IF NOT EXISTS ADD CONSTRAINT tickets_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

じゃあどうすればいいのか。このサイトを参考にさせてもらった。 http://blog.timmattison.com/archives/2014/09/02/checking-postgresql-to-see-if-a-constraint-already-exists/

create_constraint_if_not_existsという関数を作成する。関数内の処理で引数にとったconstraint_nameを抽出し、存在しなければDDLを実行するというやり方である。

CREATE OR REPLACE FUNCTION create_constraint_if_not_exists (t_name text, c_name text, constraint_sql text) RETURNS void AS
$$
BEGIN
  if not exists (SELECT constraint_name
                 FROM information_schema.constraint_column_usage
                 WHERE constraint_name = c_name) THEN
      execute 'ALTER TABLE ' || t_name || ' ADD CONSTRAINT ' || c_name || ' ' || constraint_sql;
  end if;
END;
$$
LANGUAGE plpgsql VOLATILE;

-- ALTER TABLE tickets ADD CONSTRAINT tickets_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
SELECT create_constraint_if_not_exists(
        'tickets',
        'tickets_user_id_fkey',
        'FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE'
);

ちなみにマイグレーションツールにgooseを使う場合は、関数定義を’– +goose StatementBegin’と’– +goose StatementEnd’で囲む必要があるので注意。

-- +goose Up
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION create_constraint_if_not_exists (t_name text, c_name text, constraint_sql text) RETURNS void AS
 -- 省略 --
;
-- +goose StatementEnd

Back to posts