助け合いフォーラム
正解
ON DELETE CASCADEオプションを指定すると、親表の行を削除する際に、親表を参照している子表の行も自動的に削除する
FOREIGN KEY制約を定義すると、依存する行の有無にかかわらず親表を削除できない
解説
FOREIGN KEY制約の親表に指定された表は、依存する行の有無にかかわらず削除することができません。
また、親表のデータを削除した場合の子表の振る舞いは、
・ON DELETE CASCADEオプション
・ON DELETE SET NULLオプション
を指定して設定できます。
ON DELETE CASCADEでは、親表の行が削除された場合、参照していた子表の行も同時に削除されます。
また、ON DELETE SET NULLでは、親表の行が削除された場合、参照していた子表の列にNULL値を設定します。
以上より、
・ON DELETE CASCADEオプションを指定すると、親表の行を削除する際に、親表を参照している子表の行も自動的に削除する
・FOREIGN KEY制約を定義すると、依存する行の有無にかかわらず親表を削除できない
が正解となります。
その他の選択肢については次のとおりです。
・ON DELETE CASCADEオプションを指定すると、親表の行を削除する際に、親表を参照している子表の行にNULL値を設定する
ON DELETE SET NULLの説明です。
・ON DELETE SET NULLオプションを指定すると、親表の行を削除する際に、親表を参照している子表の行も自動的に削除する
ON DELETE CASCADEの説明です。
・FOREIGN KEY制約を定義すると、依存する行がある場合のみ親表を削除できない
FOREIGN KEY制約の親表に指定された表は、依存する行の有無にかかわらず削除できません。
参考
列または列の組合せにFOREIGN KEY制約を定義すると、定義された列または列の組合せには参照先の列に存在する値しか登録できなくなります。ただしNULL値は登録することができ、複数の行にNULL値を登録する事もできます。
FOREIGN KEY制約は、次のように定義します。
[列レベルで定義する場合]
[CONSTRAINT 制約名] REFERENCES 親表名(参照する列[, 参照する列 …])
[表レベルで定義する場合]
[CONSTRAINT 制約名] FOREIGN KEY(列名[, 列名 …]) REFERENCES 親表名(参照する列[, 参照する列 …])
(
id NUMBER(2) CONSTRAINT id_pk PRIMARY KEY,
deptname VARCHAR2(10)
);
CREATE TABLE child
(
id NUMBER(2),
name VARCHAR2(10),
deptid NUMBER(2)
CONSTRAINT dept_fk REFERENCES parent(id)
);
INSERT INTO parent VALUES (1, 'Sales');
INSERT INTO child VALUES (1, 'Tanaka', 1);
INSERT INTO child VALUES (2, 'Sasaki', 2);
複数の列の組合せに対してFOREIGN KEY制約を定義する場合は、表レベルで定義します。
(
id NUMBER(2),
deptname VARCHAR2(10),
CONSTRAINT parent_pk PRIMARY KEY (id, deptname)
);
CREATE TABLE child
(
id NUMBER(2),
name VARCHAR2(10),
deptid NUMBER(2),
deptname VARCHAR2(10),
CONSTRAINT child_fk FOREIGN KEY (deptid, deptname)
REFERENCES parent (id, deptname)
);
FOREIGN KEY制約で参照できる親表の列は、PRIMARY KEY制約またはUNIQUE制約が定義されている列だけです。
(
id NUMBER(2),
deptname VARCHAR2(10)
);
CREATE TABLE child
(
id NUMBER(2),
name VARCHAR2(10),
deptid NUMBER(2)
CONSTRAINT dept_fk REFERENCES parent(id)
);
また、参照する親表に、FOREIGN KEY制約を定義する自表を指定することもできます。
(
id NUMBER(2) CONSTRAINT child_pk PRIMARY KEY,
name VARCHAR2(10),
manager NUMBER(2)
CONSTRAINT mgr_fk REFERENCES child(id)
);
FOREIGN KEY制約の親表として指定された表は、参照されている行がない場合でも削除できなくなります。親表を削除したい場合は、FOREIGN KEY制約を定義した表を削除してから親表を削除します。
(
id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
name VARCHAR2(10)
);
CREATE TABLE child
(
id NUMBER(2),
name VARCHAR2(10),
deptid NUMBER(2)
CONSTRAINT dept_fk REFERENCES parent(id)
);
DROP TABLE parent;
FOREIGN KEY制約では、ON DELETE CASCADEオプションやON DELETE SET NULLオプションと組み合せて定義することもできます。これらのオプションを指定することで、親表のデータを削除した場合に、FOREIGN KEY制約を定義した子表のデータをどのようにするかを指定できます。
(
id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
dept_name VARCHAR2(10)
);
CREATE TABLE child
(
id NUMBER(2),
name VARCHAR2(10),
deptid NUMBER(2) CONSTRAINT dept_fk REFERENCES parent (id)
);
INSERT INTO parent VALUES (1, 'SALES');
INSERT INTO child VALUES (1, 'Tanaka', 1);
DELETE FROM parent WHERE id = 1;
(
id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
dept_name VARCHAR2(10));
CREATE TABLE child
(
id NUMBER(2),
name VARCHAR2(10),
deptid NUMBER(2)
CONSTRAINT dept_fk REFERENCES parent (id) ON DELETE CASCADE
);
INSERT INTO parent VALUES (1, 'SALES');
INSERT INTO child VALUES (1, 'Tanaka', 1);
DELETE FROM parent WHERE id = 1;
SELECT * FROM child;
(
id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
dept_name VARCHAR2(10)
);
CREATE TABLE child
(
id NUMBER(2),
name VARCHAR2(10),
deptid NUMBER(2)
CONSTRAINT dept_fk REFERENCES parent (id)
ON DELETE SET NULL
);
INSERT INTO parent VALUES (1, 'SALES');
INSERT INTO child VALUES (1, 'Tanaka', 1);
DELETE FROM parent WHERE id = 1;
SELECT * FROM child;
外部キーの親表の削除
・FOREIGN KEY制約を定義すると、依存する行がある場合のみ親表を削除できない
FOREIGN KEY制約の親表に指定された表は、依存する行の有無にかかわらず削除できません
解説に上記記載がありますが間違いですよね？
cascade constraintsをつければ親表も削除できます。↓
SQL> drop table parent cascade constraints;
表が削除されました。
Oracle® Database SQL言語リファレンスより
CASCADE CONSTRAINTS
CASCADE CONSTRAINTSを指定すると、削除される表の主キーまたは一意キーを参照するすべての参照整合性制約を削除できます。このような参照整合性制約があるときにこの句を省略した場合、エラーが戻され、表は削除されません。
上記の書き方だと、参照整合性制約を削除した上で表を削除するんだと思います。
コメント
この投稿に対して返信しませんか？
m mmmss125
2023/01/26 15:03
ありがとうございます。 つまり、「cascade constraintsで参照整合制約を削除せずに親表を削除する方法の有無」を問われているのでしょうか。。。？