助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)

問題ID : 26563

問題を開く

FOREIGN KEY制約の説明として正しいものはどれですか(該当するものを全て選択して下さい)。

ON DELETE CASCADEオプションを指定すると、親表の行を削除する際に、親表を参照している子表の行も自動的に削除する

ON DELETE CASCADEオプションを指定すると、親表の行を削除する際に、親表を参照している子表の行にNULL値を設定する

ON DELETE SET NULLオプションを指定すると、親表の行を削除する際に、親表を参照している子表の行も自動的に削除する

FOREIGN KEY制約を定義すると、依存する行がある場合のみ親表を削除できない

FOREIGN KEY制約を定義すると、依存する行の有無にかかわらず親表を削除できない

正解

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 親表名(参照する列[, 参照する列 …])

SQLを表示

CREATE TABLE parent
(
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);

<img src="/mondai3/img/jpg/kk49116.jpg">

複数の列の組合せに対してFOREIGN KEY制約を定義する場合は、表レベルで定義します。

SQLを表示

CREATE TABLE parent
(
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制約が定義されている列だけです。

SQLを表示

CREATE TABLE parent
(
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制約を定義する自表を指定することもできます。

SQLを表示

CREATE TABLE child
(
id NUMBER(2) CONSTRAINT child_pk PRIMARY KEY,
name VARCHAR2(10),
manager NUMBER(2)
CONSTRAINT mgr_fk REFERENCES child(id)
);

FOREIGN KEY制約の親表として指定された表は、参照されている行がない場合でも削除できなくなります。親表を削除したい場合は、FOREIGN KEY制約を定義した表を削除してから親表を削除します。

SQLを表示

CREATE TABLE parent
(
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制約を定義した子表のデータをどのようにするかを指定できます。

SQLを表示

CREATE TABLE parent
(
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;

SQLを表示

CREATE TABLE parent
(
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;

SQLを表示

CREATE TABLE parent
(
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;

参考URL

Oracle® Database概要「5 データ整合性」: 外部キー制約

Oracle® Database SQL言語リファレンス「constraint」: 外部キー制約

上に戻る

外部キーの親表の削除

m mmmss125

投稿日 2023/01/25

・FOREIGN KEY制約を定義すると、依存する行がある場合のみ親表を削除できない
FOREIGN KEY制約の親表に指定された表は、依存する行の有無にかかわらず削除できません

解説に上記記載がありますが間違いですよね？
cascade constraintsをつければ親表も削除できます。↓

SQL> drop table parent cascade constraints;

表が削除されました。

M Mottchan

2023/01/26 09:48

Oracle® Database SQL言語リファレンスより

CASCADE CONSTRAINTS
CASCADE CONSTRAINTSを指定すると、削除される表の主キーまたは一意キーを参照するすべての参照整合性制約を削除できます。このような参照整合性制約があるときにこの句を省略した場合、エラーが戻され、表は削除されません。

上記の書き方だと、参照整合性制約を削除した上で表を削除するんだと思います。

m mmmss125

2023/01/26 15:03

ありがとうございます。つまり、「cascade constraintsで参照整合制約を削除せずに親表を削除する方法の有無」を問われているのでしょうか。。。？

M Mottchan

2023/01/27 14:14

そうだと思います。デフォルトは親表を削除できないので、デフォルト値での解答欄が作られているのだと思います。ただ、言葉の定義が曖昧な感じはありますね。 FOREIGN KEY制約の親表に指定された表は、依存する行の有無にかかわらず削除できません。 →"FOREIGN KEY制約の親表に指定された表は"となっているので、cascade constraintsで制約指定を外さない限り、親表に制約がかかっているため削除できないと分かる。・FOREIGN KEY制約を定義すると、依存する行がある場合のみ親表を削除できない →定義しても、親表をcascade constraintsで削除できるじゃん。となります。なので、・FOREIGN KEY制約の親表に指定された表は、依存する行がある場合のみ親表を削除できない。というような感じで、表に対して参照整合性制約がかかっている状態の表を主語に置く必要がありそうですね。

この投稿に対して返信しませんか？

助け合いフォーラム