SQL Server外部キー制約
6703 ワード
外部キー制約のテストテーブルとテストデータ
--テストマスターテーブルを作成します.IDはプライマリキーである.
CREATE TABLE test_main (
id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);
--テストサブテーブルを作成します.
CREATE TABLE test_sub (
id INT,
main_id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);
--テストマスターテーブルのデータを挿入します.
INSERT INTO test_main(id, value) VALUES (1, 'ONE');
INSERT INTO test_main(id, value) VALUES (2, 'TWO');
--テストサブテーブルのデータを挿入します.
INSERT INTO test_sub(id, main_id, value) VALUES (1, 1, 'ONEONE');
INSERT INTO test_sub(id, main_id, value) VALUES (2, 2, 'TWOTWO');
既定の外部キーコンストレイント
1> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main;
2> go
1> DELETE
2> test_main
3> WHERE
4> ID = 1;
5> go
メッセージ547、レベル16、ステータス1、サーバHOME-BED 592453 CSQLEXPRESS、1行目
DELETE文は、REFERENCE制約「main_id_cons」と競合します.この競合は、データベース「Stock」、テーブル「db」で発生します.
o.test_sub", column 'main_id'.
文は終了しました.
テストが完了したら、外部キー制約を削除します.
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
DELETE CASCADE方式
1>--外部キーの作成(ON DELETE CASCADEオプションを使用して、プライマリ・テーブルを削除するときにサブテーブルを削除)
2> ALTER TABLE test_sub
3> ADD CONSTRAINT main_id_cons
4> FOREIGN KEY (main_id) REFERENCES test_main ON DELETE CASCADE;
5>
6>--マスターテーブルデータの削除をテストします.正常に実行されます.
7> DELETE
8> TEST_MAIN
9> WHERE
10> ID = 1;
11>
12>--チェックリストをテストします.main_が1つしかないはずです.id=2のデータ
13> SELECT
14> *
15> FROM
16> test_sub;
17>
18> go
(1行に影響)
id main_id value
----------- ----------- ----------
2 2 TWOTWO
(1行に影響)
テストが完了したら、外部キー制約を削除します.
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
UPDATE CASCADE方式
--外部キーの作成(ON UPDATE CASCADEオプションを使用して、プライマリ・テーブルのプライマリ・キーを更新すると同時に、サブテーブルの外部キーを更新)
1> ALTER TABLE test_sub
2> ADD CONSTRAINT main_id_cons
3> FOREIGN KEY (main_id) REFERENCES test_main ON UPDATE CASCADE;
4> go
--マスターテーブルデータの更新をテストします.正常に実行されます.
1> UPDATE test_main SET id = 5 where id = 1
2> go
(1行に影響)
--サブテーブルデータが同期的に更新されているかどうかを確認します.
1> select * from test_sub
2> go
id main_id value
----------- ----------- ----------
1 5 ONEONE
2 2 TWOTWO
テストが完了したら、外部キー制約を削除します.
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
SET NULL方式
1>--外部キーを作成します(ON DELETE SET NULLオプションを使用してプライマリ・テーブルを削除すると、サブテーブルのmain_i
d NULLに設定)
2> ALTER TABLE test_sub
3> ADD CONSTRAINT main_id_cons
4> FOREIGN KEY (main_id) REFERENCES test_main ON DELETE SET NULL;
5>
6>
7>--マスターテーブルデータの削除をテストします.正常に実行されます.
8> DELETE
9> TEST_MAIN
10> WHERE
11> ID = 2;
12>
13>--チェックリストをテストします.mainがあるはずです.id = null
14> SELECT
15> *
16> FROM
17> test_sub;
18>
19> go
(1行に影響)
id main_id value
----------- ----------- ----------
2 NULL TWOTWO
(1行に影響)
テストが完了したら、外部キー制約を削除します.
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
SET DEFAULT方式
分かりやすいように、SET DEFAULT方式では、以下のようなテストテーブルとテストデータを用いる.
まずON DELETE SET DEFAULTをテストします
ALTER TABLE test_sub_student
ADD CONSTRAINT fk_main_class
FOREIGN KEY (main_id) REFERENCES test_main_class ON DELETE SET DEFAULT;
SELECT
test_main_class.value AS「クラス」、
test_sub_student.value AS「学生」
FROM
test_main_class
JOIN test_sub_student
ON(test_main_class.id = test_sub_student.main_id);
go
クラスの学生
-------------------- ----------
2013級1組張三
2013級2組李四
(2行の影響)
1> DELETE FROM test_main_class WHERE ID = 1;
2> go
(1行に影響)
1> SELECT
2> test_main_class.value AS「クラス」、
3> test_sub_student.value AS「学生」
4> FROM
5> test_main_class
6> JOIN test_sub_student
7> ON(test_main_class.id = test_sub_student.main_id);
8> go
クラスの学生
-------------------- ----------
クラスの張三はしばらくない
2013級2組李四
(2行の影響)
1>
ON DELETE SET DEFAULT
ローを削除しようとすると、
ローのキーは、他のテーブルの既存のローの外部キーによって参照されます.
参照行の外部キーを構成するすべての値がデフォルト値に設定されます.
この制約を実行するには、ターゲット・テーブルのすべての外部キー列にデフォルト定義が必要です.
カラムがNULLであり、明示的なデフォルト値が設定されていない場合は、NULLがカラムの暗黙的なデフォルト値として使用されます.
外部キー制約の有効性を維持するには、ON DELETE SET DEFAULTによって設定されたNULL以外の値は、プライマリ・テーブルに対応する値が必要です.
次にON UPDATE SET DEFAULTをテストする
--前の外部キー制約を削除します.
ALTER TABLE test_sub_student DROP CONSTRAINT fk_main_class;
GO
--外部キー制約を作成します.
ALTER TABLE test_sub_student
ADD CONSTRAINT fk_main_class
FOREIGN KEY (main_id) REFERENCES test_main_class ON UPDATE SET DEFAULT;
--マスターテーブルを更新します.
1> UPDATE test_main_class SET ID = 20 WHERE ID = 2;
2> go
(1行に影響)
1>
2> SELECT
3> test_main_class.value AS「クラス」、
4> test_sub_student.value AS「学生」
5> FROM
6> test_main_class
7> JOIN test_sub_student
8> ON(test_main_class.id = test_sub_student.main_id);
9> go
クラスの学生
-------------------- ----------
クラスの張三はしばらくない
クラスの李四はしばらくない.
(2行の影響)
ON UPDATE SET DEFAULT
ローを更新しようとすると、
ローのキーは、他のテーブルの既存のローの外部キーによって参照されます.
参照行の外部キーを構成するすべての値がデフォルト値に設定されます.
この制約を実行するには、ターゲット・テーブルのすべての外部キー列にデフォルト定義が必要です.
カラムがNULLであり、明示的なデフォルト値が設定されていない場合は、NULLがカラムの暗黙的なデフォルト値として使用されます.
外部キー制約の有効性を維持するには、ON UPDATE SET DEFAULTによって設定されたNULL以外の値は、プライマリ・テーブルに対応する値が必要です.
--テストマスターテーブルを作成します.IDはプライマリキーである.
CREATE TABLE test_main (
id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);
--テストサブテーブルを作成します.
CREATE TABLE test_sub (
id INT,
main_id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);
--テストマスターテーブルのデータを挿入します.
INSERT INTO test_main(id, value) VALUES (1, 'ONE');
INSERT INTO test_main(id, value) VALUES (2, 'TWO');
--テストサブテーブルのデータを挿入します.
INSERT INTO test_sub(id, main_id, value) VALUES (1, 1, 'ONEONE');
INSERT INTO test_sub(id, main_id, value) VALUES (2, 2, 'TWOTWO');
既定の外部キーコンストレイント
1> ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main;
2> go
1> DELETE
2> test_main
3> WHERE
4> ID = 1;
5> go
メッセージ547、レベル16、ステータス1、サーバHOME-BED 592453 CSQLEXPRESS、1行目
DELETE文は、REFERENCE制約「main_id_cons」と競合します.この競合は、データベース「Stock」、テーブル「db」で発生します.
o.test_sub", column 'main_id'.
文は終了しました.
テストが完了したら、外部キー制約を削除します.
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
DELETE CASCADE方式
1>--外部キーの作成(ON DELETE CASCADEオプションを使用して、プライマリ・テーブルを削除するときにサブテーブルを削除)
2> ALTER TABLE test_sub
3> ADD CONSTRAINT main_id_cons
4> FOREIGN KEY (main_id) REFERENCES test_main ON DELETE CASCADE;
5>
6>--マスターテーブルデータの削除をテストします.正常に実行されます.
7> DELETE
8> TEST_MAIN
9> WHERE
10> ID = 1;
11>
12>--チェックリストをテストします.main_が1つしかないはずです.id=2のデータ
13> SELECT
14> *
15> FROM
16> test_sub;
17>
18> go
(1行に影響)
id main_id value
----------- ----------- ----------
2 2 TWOTWO
(1行に影響)
テストが完了したら、外部キー制約を削除します.
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
UPDATE CASCADE方式
--外部キーの作成(ON UPDATE CASCADEオプションを使用して、プライマリ・テーブルのプライマリ・キーを更新すると同時に、サブテーブルの外部キーを更新)
1> ALTER TABLE test_sub
2> ADD CONSTRAINT main_id_cons
3> FOREIGN KEY (main_id) REFERENCES test_main ON UPDATE CASCADE;
4> go
--マスターテーブルデータの更新をテストします.正常に実行されます.
1> UPDATE test_main SET id = 5 where id = 1
2> go
(1行に影響)
--サブテーブルデータが同期的に更新されているかどうかを確認します.
1> select * from test_sub
2> go
id main_id value
----------- ----------- ----------
1 5 ONEONE
2 2 TWOTWO
テストが完了したら、外部キー制約を削除します.
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
SET NULL方式
1>--外部キーを作成します(ON DELETE SET NULLオプションを使用してプライマリ・テーブルを削除すると、サブテーブルのmain_i
d NULLに設定)
2> ALTER TABLE test_sub
3> ADD CONSTRAINT main_id_cons
4> FOREIGN KEY (main_id) REFERENCES test_main ON DELETE SET NULL;
5>
6>
7>--マスターテーブルデータの削除をテストします.正常に実行されます.
8> DELETE
9> TEST_MAIN
10> WHERE
11> ID = 2;
12>
13>--チェックリストをテストします.mainがあるはずです.id = null
14> SELECT
15> *
16> FROM
17> test_sub;
18>
19> go
(1行に影響)
id main_id value
----------- ----------- ----------
2 NULL TWOTWO
(1行に影響)
テストが完了したら、外部キー制約を削除します.
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;
SET DEFAULT方式
分かりやすいように、SET DEFAULT方式では、以下のようなテストテーブルとテストデータを用いる.
-- . ID .
-- .
CREATE TABLE test_main_class (
id INT NOT NULL,
value VARCHAR(20),
PRIMARY KEY(id)
);
-- .
-- .
CREATE TABLE test_sub_student (
id INT NOT NULL,
main_id INT DEFAULT 0,
value VARCHAR(10),
PRIMARY KEY(id)
);
-- .
INSERT INTO test_main_class(id, value) VALUES (0, ' ');
INSERT INTO test_main_class(id, value) VALUES (1, '2013 1 ');
INSERT INTO test_main_class(id, value) VALUES (2, '2013 2 ');
-- .
INSERT INTO test_sub_student(id, main_id, value) VALUES (1, 1, ' ');
INSERT INTO test_sub_student(id, main_id, value) VALUES (2, 2, ' ');
まずON DELETE SET DEFAULTをテストします
ALTER TABLE test_sub_student
ADD CONSTRAINT fk_main_class
FOREIGN KEY (main_id) REFERENCES test_main_class ON DELETE SET DEFAULT;
SELECT
test_main_class.value AS「クラス」、
test_sub_student.value AS「学生」
FROM
test_main_class
JOIN test_sub_student
ON(test_main_class.id = test_sub_student.main_id);
go
クラスの学生
-------------------- ----------
2013級1組張三
2013級2組李四
(2行の影響)
1> DELETE FROM test_main_class WHERE ID = 1;
2> go
(1行に影響)
1> SELECT
2> test_main_class.value AS「クラス」、
3> test_sub_student.value AS「学生」
4> FROM
5> test_main_class
6> JOIN test_sub_student
7> ON(test_main_class.id = test_sub_student.main_id);
8> go
クラスの学生
-------------------- ----------
クラスの張三はしばらくない
2013級2組李四
(2行の影響)
1>
ON DELETE SET DEFAULT
ローを削除しようとすると、
ローのキーは、他のテーブルの既存のローの外部キーによって参照されます.
参照行の外部キーを構成するすべての値がデフォルト値に設定されます.
この制約を実行するには、ターゲット・テーブルのすべての外部キー列にデフォルト定義が必要です.
カラムがNULLであり、明示的なデフォルト値が設定されていない場合は、NULLがカラムの暗黙的なデフォルト値として使用されます.
外部キー制約の有効性を維持するには、ON DELETE SET DEFAULTによって設定されたNULL以外の値は、プライマリ・テーブルに対応する値が必要です.
次にON UPDATE SET DEFAULTをテストする
--前の外部キー制約を削除します.
ALTER TABLE test_sub_student DROP CONSTRAINT fk_main_class;
GO
--外部キー制約を作成します.
ALTER TABLE test_sub_student
ADD CONSTRAINT fk_main_class
FOREIGN KEY (main_id) REFERENCES test_main_class ON UPDATE SET DEFAULT;
--マスターテーブルを更新します.
1> UPDATE test_main_class SET ID = 20 WHERE ID = 2;
2> go
(1行に影響)
1>
2> SELECT
3> test_main_class.value AS「クラス」、
4> test_sub_student.value AS「学生」
5> FROM
6> test_main_class
7> JOIN test_sub_student
8> ON(test_main_class.id = test_sub_student.main_id);
9> go
クラスの学生
-------------------- ----------
クラスの張三はしばらくない
クラスの李四はしばらくない.
(2行の影響)
ON UPDATE SET DEFAULT
ローを更新しようとすると、
ローのキーは、他のテーブルの既存のローの外部キーによって参照されます.
参照行の外部キーを構成するすべての値がデフォルト値に設定されます.
この制約を実行するには、ターゲット・テーブルのすべての外部キー列にデフォルト定義が必要です.
カラムがNULLであり、明示的なデフォルト値が設定されていない場合は、NULLがカラムの暗黙的なデフォルト値として使用されます.
外部キー制約の有効性を維持するには、ON UPDATE SET DEFAULTによって設定されたNULL以外の値は、プライマリ・テーブルに対応する値が必要です.