【SQL Server】外部参照制約を無効化/有効化するSQLの検証

結論

以下のSQLで外部参照制約の無効化/有効化が可能

●外部参照制約を無効化
ALTER TABLE [テーブル名] NOCHECK CONSTRAINT [外部参照名];
→テーブル「sys.foreign_keys」の以下の列が「1」となる
　・is_disabled
　・is_not_trusted

●外部参照制約を有効化
ALTER TABLE [テーブル名] WITH CHECK CHECK CONSTRAINT [外部参照名];
→テーブル「sys.foreign_keys」の以下の列が「0」となる
　・is_disabled
　・is_not_trusted

検証(準備)

１.作成する外部参照制約が存在しないことを確認

SELECT
	name,
	is_disabled,
	is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Department_ID'

２.確認用のテーブルを2つを作成
・部署テーブル
・社員テーブル
　※列「Department_ID」に外部参照制約を設定。参照先は部署テーブルの列「Id」

--部署テーブル
CREATE TABLE Department(
	[Id] NVARCHAR(30) NOT NULL PRIMARY KEY,
	[Name] NVARCHAR(30) NOT NULL
)

--社員テーブル
CREATE TABLE Employee(
	[Id] NVARCHAR(30) NOT NULL PRIMARY KEY,
	[Name] NVARCHAR(30) NOT NULL,
	[Department_ID] NVARCHAR(30),
	constraint FK_Department_ID foreign key([Department_ID]) references Department([Id])
)

３.外部参照制約が作成されていることを確認する

SELECT
	name,
	is_disabled,
	is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Department_ID'

４.確認用のテーブルにデータをINSERTする

--部署テーブル
INSERT INTO Department VALUES ('001','営業部')

--社員テーブル
INSERT INTO Employee VALUES ('20200405001','鈴木','001')

--INSERTされたことを確認
SELECT * FROM Department
SELECT * FROM Employee

検証(外部参照制約を無効化)

１.外部参照制約が有効であることを確認するため、不正なデータをINSERTし
　エラーとなることを確認

--社員テーブル
INSERT INTO Employee VALUES ('20200405002','鈴木','auieo')

２.外部参照制約を無効化するSQLを実行

--外部参照制約を無効化
ALTER TABLE Employee NOCHECK CONSTRAINT FK_Department_ID;

３.外部参照制約が無効化されていることを確認

SELECT
	name,
	is_disabled,
	is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Department_ID'

４.外部参照制約が無効であることを確認するため、不正なデータをINSERTし
　正常終了することを確認

--社員テーブル
INSERT INTO Employee VALUES ('20200405002','鈴木','auieo')

検証(外部参照制約を有効化)

１.有効化時にエラーとならないよう不正なデータをDELETE

--社員テーブル
DELETE FROM Employee WHERE Id = '20200405002'

２.外部参照制約を有効化するSQLを実行

--外部参照制約を有効化
ALTER TABLE Employee WITH CHECK CHECK CONSTRAINT FK_Department_ID;

３.外部参照制約が有効化されていることを確認

SELECT
	name,
	is_disabled,
	is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Department_ID'

４.外部参照制約が有効であることを確認するため、不正なデータをINSERTし
　エラーとなることを確認

--社員テーブル
INSERT INTO Employee VALUES ('20200405002','鈴木','auieo')

後片付け

１.確認用のテーブルを2つを削除

--社員テーブル
DROP TABLE Employee

--部署テーブル
DROP TABLE Department

２.外部参照制約も削除されていることを確認

SELECT
	name,
	is_disabled,
	is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Department_ID'

備考

外部参照の無効化/有効化を実行した日時は、カラム「modify_date」で確認できる

SELECT
	name,
	is_disabled,
	is_not_trusted,
	modify_date
FROM sys.foreign_keys
WHERE name = 'FK_Department_ID'