DB別 外部キー参照制約を無効にするコマンド

DB別 外部キー参照制約を無効にするコマンド

Repositoryのテストでデータをテーブルにセットアップする時などに、外部キー制約が張られていて参照制約違反エラーとなるパターンがよくあります。

毎回DB別で参照制約を無効にするコマンドをググっているので、自分の分かっている限りのものをまとめておきます。

H2DB

外部キーの個別での一時的な無効はできない。
基本は外部キーの削除→追加のパターン。
ただ、外部キーの制約名を指定してないとシステムが勝手に名付けるから無理。
外部キーまるごとの無効はできる。

外部キーの削除→追加

削除

ALTER TABLE [tableName] DROP FOREIGN KEY [foreignKeyName];

追加

テーブル作成時のcreate文見るべし。

外部キー制約を丸ごと無効→有効

無効

SET REFERENTIAL_INTEGRITY FALSE

有効

SET REFERENTIAL_INTEGRITY TRUE

MySQL

H2DBと共通のコマンドが多いっぽい。

外部キーの削除→追加

削除

ALTER TABLE [tableName] DROP FOREIGN KEY [foreignKeyName];

追加

テーブル作成時のcreate文見るべし。

外部キー制約を丸ごと無効→有効

無効

SET FOREIGN_KEY_CHECKS=0;

有効

SET FOREIGN_KEY_CHECKS=1;

postgreSQL

postgreSQLはテーブル単位で外部キーの無効と有効が可能
丸ごと無効にするコマンドもあるみたいだけど前提として外部キー作成時の設定が必要な模様。

テーブル単位で外部キーの無効→有効

無効

ALTER TABLE [tableName] DISABLE TRIGGER ALL;

有効

ALTER TABLE [tableName] ENABLE TRIGGER ALL;

外部キー制約を丸ごと無効→有効

前提条件

外部キー制約生成時に、DEFERRABLE性質を与えておかなければならない。
具体的に言うと以下のような外部キーの生成でオプション指定しておく必要がある。

CREATE TABLE hoge (
  fuga serial PRIMARY KEY,
  poge int
  CONSTRAINT [foreignKeyName] FOREIGN KEY ([columnName]) REFERENCES [toForeignTableName]([toForeignColumnName]) DEFERRABLE
); 

無効

SET CONSTRAINTS ALL DEFERRED;

有効

SET CONSTRAINTS ALL IMMEDIATE;

DB2

公式のドキュメントが読みにくくて仕方がないDB2。
外部キー個別は削除→追加。
テーブルごとに無効→有効ができる。

外部キーの削除→追加

削除

ALTER TABLE [tableName] DROP FOREIGN KEY [foreignKeyName]

追加

テーブル作成時のcreate文見るべし。

テーブルごとに無効→有効

無効

SET INTEGRITY FOR [tableName] OFF;

有効

SET INTEGRITY FOR [tableName] IMMEDIATE CHECKED;

SQLite

スマホアプリの内部DBとして使われているイメージ。
外部キーを生成しただけでは有効にならない模様。
外部キーを有効にするコマンドを叩いて初めて外部キーが有効になるらしい。

外部キーの無効→有効

無効

PRAGMA foreign_keys = false;

有効

PRAGMA foreign_keys = true;

参考

H2DB

MySQL と H2 の両方で動作するデータ定義言語 (DDL) のパターン集
Play framework Slick 3 how to disable H2 foreign key constraints by scala?
H2DB公式リファレンス

MySQL

MySQLで外部キー付きテーブルをSET FOREIGN_KEY_CHECKS=0;でリストアしているのに外部キー制約違反が出るとき
外部キー制約を無視してデータを操作する

postgreSQL

PostgreSQLを使ったユニットテストをするときに、外部キーを一時的に無効化する
PostgreSQLでトランザクション中の外部キーチェックを外す
PostgreSQL 9.5.4文書 SET CONSTRAINTS
PostgreSQL の外部キー制約の簡単なサンプルメモ

DB2

DB2公式リファレンス 制約のドロップ
DB2公式リファレンス 制約の作成および変更
DB2のLOADコマンド

SQLite

How do I disable constraint checking during schema migration?
sqlite3で外部キーを有効にする

DBsetupカテゴリの最新記事