助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26903
問題を開く
次のSQL文を実行しました。
CREATE TABLE parent
(
id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
dept_name VARCHAR2(10)
);
CREATE TABLE child
(
id NUMBER(2) CONSTRAINT cid_pk PRIMARY KEY,
name VARCHAR2(10) CONSTRAINT cname_uq UNIQUE,
deptid NUMBER(2) CONSTRAINT dept_fk REFERENCES parent (id) ON DELETE CASCADE
);
INSERT INTO parent VALUES (1, 'parent');
INSERT INTO child VALUES (10, 'child', 1);
これらの表に対して、正常に実行できるSQL文はどれですか(2つ選択して下さい)。
CREATE TABLE parent
(
id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
dept_name VARCHAR2(10)
);
CREATE TABLE child
(
id NUMBER(2) CONSTRAINT cid_pk PRIMARY KEY,
name VARCHAR2(10) CONSTRAINT cname_uq UNIQUE,
deptid NUMBER(2) CONSTRAINT dept_fk REFERENCES parent (id) ON DELETE CASCADE
);
INSERT INTO parent VALUES (1, 'parent');
INSERT INTO child VALUES (10, 'child', 1);
これらの表に対して、正常に実行できるSQL文はどれですか(2つ選択して下さい)。
正解
ALTER TABLE child DROP (id);
ALTER TABLE parent DROP COLUMN id CASCADE CONSTRAINTS;
解説
ALTER TABLE文で、既存の表の列の削除を行えます。
ALTER TABLE 表名 DROP ( 列名 [, 列名...]) [CASCADE CONSTRAINTS];
※1つの列のみ削除する場合は、次の構文も使用できます。
ALTER TABLE 表名 DROP COLUMN 列名 [CASCADE CONSTRAINTS];
列の削除に関しては、以下の注意事項があります。
・削除した列は戻せない
・表には最低1つの列を残す必要がある
・他から参照されるFOREIGN KEY制約の親表の列はCASCADE CONSTRAINTSオプションを指定する必要がある
・パーティション化キー列（表をパーティション化するためのキー列）は削除できない
・列に多くのデータが含まれている場合は、削除に時間がかかる
設問のSQL文では、CHILD表のDEPTID列に、PARENT表のID列を参照するFOREIGN KEY制約を定義しています。その際、ON DELETEオプションを指定していますが、親表の行を削除した場合の動作なので、親表の列の削除には関係ありません。PARENT表のID列を削除するにはCASCADE CONSTRAINTSオプションが必要です。
他の表や列から参照されていない制約の列はCASCADEオプションなしで削除が可能です。その際、列に定義されている制約も同時に削除されます。
以上より、
・ALTER TABLE child DROP (id);
・ALTER TABLE parent DROP COLUMN id CASCADE CONSTRAINTS;
が正解となります。
正解のSQL文の実行結果は次のようになります。
※各文を実行する度に、① DROP TABLE child PURGE; ② DROP TABLE parent PURGE; ③ 設問のSQL文を実行します。
その他の選択肢については次のとおりです。
・ALTER TABLE parent DROP (id);
CHILD表のDEPTID列から参照されているPRIMARY KEY制約の列なので、CASCADE CONSTRAINTSオプションが必要です。「親キーの列は削除できません。」エラーとなり、誤ったSQL文です。
・ALTER TABLE child DROP (id, name, deptid);
CHILD表の列を全て削除することはできないので「表の列をすべて削除することはできません。」エラーとなり、誤ったSQL文です。
・ALTER TABLE child DROP COLUMN;
COLUMN句の後に列名が指定されていないため、構文エラーとなります。誤ったSQL文です。
ALTER TABLE 表名 DROP ( 列名 [, 列名...]) [CASCADE CONSTRAINTS];
※1つの列のみ削除する場合は、次の構文も使用できます。
ALTER TABLE 表名 DROP COLUMN 列名 [CASCADE CONSTRAINTS];
列の削除に関しては、以下の注意事項があります。
・削除した列は戻せない
・表には最低1つの列を残す必要がある
・他から参照されるFOREIGN KEY制約の親表の列はCASCADE CONSTRAINTSオプションを指定する必要がある
・パーティション化キー列（表をパーティション化するためのキー列）は削除できない
・列に多くのデータが含まれている場合は、削除に時間がかかる
設問のSQL文では、CHILD表のDEPTID列に、PARENT表のID列を参照するFOREIGN KEY制約を定義しています。その際、ON DELETEオプションを指定していますが、親表の行を削除した場合の動作なので、親表の列の削除には関係ありません。PARENT表のID列を削除するにはCASCADE CONSTRAINTSオプションが必要です。
他の表や列から参照されていない制約の列はCASCADEオプションなしで削除が可能です。その際、列に定義されている制約も同時に削除されます。
以上より、
・ALTER TABLE child DROP (id);
・ALTER TABLE parent DROP COLUMN id CASCADE CONSTRAINTS;
が正解となります。
正解のSQL文の実行結果は次のようになります。
※各文を実行する度に、① DROP TABLE child PURGE; ② DROP TABLE parent PURGE; ③ 設問のSQL文を実行します。
SQLを表示
DESC child
ALTER TABLE child DROP (id);
DESC child
ALTER TABLE child DROP (id);
DESC child
SQLを表示
DESC parent
ALTER TABLE parent DROP COLUMN id CASCADE CONSTRAINTS;
DESC parent
ALTER TABLE parent DROP COLUMN id CASCADE CONSTRAINTS;
DESC parent
その他の選択肢については次のとおりです。
・ALTER TABLE parent DROP (id);
CHILD表のDEPTID列から参照されているPRIMARY KEY制約の列なので、CASCADE CONSTRAINTSオプションが必要です。「親キーの列は削除できません。」エラーとなり、誤ったSQL文です。
・ALTER TABLE child DROP (id, name, deptid);
CHILD表の列を全て削除することはできないので「表の列をすべて削除することはできません。」エラーとなり、誤ったSQL文です。
・ALTER TABLE child DROP COLUMN;
COLUMN句の後に列名が指定されていないため、構文エラーとなります。誤ったSQL文です。
参考
ALTER TABLE文では、次の操作を行えます。
・新しい列の追加
・既存の列のデータ型の変更
・既存の列へのデフォルト値の設定
・既存の列への制約の定義
・既存の列の削除
・既存の列名の変更
・表のモード変更(読み取り/書き込みモード、読み取り専用モード)
【新しい列の追加】
表への新しい列の追加は次のように行います。
ALTER TABLE 表名 ADD
( 列名 データ型(サイズ) [DEFAULT 値] [[CONSTRAINT 制約名] 制約タイプ]
[, 列名 データ型(サイズ)] ...
);
以下は既存のPROD表にNOTES列を追加する例です。表の最後に新しい列が追加されます。
表に既存のデータが存在する場合、新しく追加された列にはデフォルトでNULL値が設定されます。表が空でない場合、NOT NULL制約を定義した列を追加しようとするとエラーとなりますが、列の追加時にNULL以外のデフォルト値を指定することで、既存のデータの列にもデフォルト値が設定されます。
以下は、既存のデータが存在するPROD3表にNOT NULL制約を定義したSTATUS列を追加する例です。列の追加時にデフォルト値を指定しないとエラーとなりますが、デフォルト値を指定すると列を追加できます。
【既存の列のデータ型、サイズ、デフォルト値の変更】
ALTER TABLE文で、既存の列のデータ型、サイズ、デフォルト値を変更できます。
ALTER TABLE 表名 MODIFY ( 列名 [データ型(サイズ)] [DEFAULT 値] [, 列名 ...]);
列の変更に関しては、以下のガイドラインがあります。
・列にNULL値のみが格納されている場合、データ型を変更できる
・列にデータが含まれている場合、列の変更後も格納できるデータ型やサイズであれば変更できる
[OK例] CHAR(10) → VARCHAR2(20), VARCHAR2(20)に10バイト以下の文字が格納されている → CHAR(10)
[NG例] CHAR(20)に10バイト以下の文字が格納されている → VARCHAR2(10)
※CHAR型は固定長のため「10バイト以下の文字 + 空白 = 20バイト」で格納される
・デフォルト値の変更は、新しい行の挿入時に反映される
【既存の列への制約の定義】
ALTER TABLE文で、既存の列へ制約を定義できます。
ALTER TABLE 表名 ADD [CONSTRAINT 制約名] 制約タイプ (列名);
ALTER TABLE 表名 MODIFY (列名 [CONSTRAINT 制約名] 制約タイプ);
但し、NOT NULL制約はALTER TABLE 文のADD CONSTRAINT句では追加できません。NOT NULL制約はMODIFY句で追加する必要があります。
また、ALTER TABLE文で既存の表の制約を無効化、もしくは有効化できます。
ALTER TABLE 表名 DISABLE CONSTRAINT 制約名 [CASCADE];
ALTER TABLE 表名 ENABLE CONSTRAINT 制約名 [CASCADE];
FOREIGN KEY制約から参照されているPRIMARY KEY制約またはUNIQUE制約をDISABLE CONSTRAINTで無効化する場合は、CASCADEオプションを指定して、無効化する制約を参照するFOREIGN KEY制約も同時に無効化しなければなりません。ENABLE CONSTRAINTで制約を有効化する場合はCASCADEオプションは指定できないので、各制約をそれぞれ有効化する必要があります。
【既存の列の削除】
既存の表の列の削除は次のように行います。
ALTER TABLE 表名 DROP ( 列名 [, 列名...]) [CASCADE CONSTRAINTS];
※1つの列のみ削除する場合は、次の構文も使用できます。
ALTER TABLE 表名 DROP COLUMN 列名 [CASCADE CONSTRAINTS];
列の削除に関しては、以下の注意事項があります。
・削除した列は戻せない
・表には最低1つの列を残す必要がある
・他から参照されるFOREIGN KEY制約の親表の列はCASCADE CONSTRAINTSオプションを指定する必要がある
・パーティション化キー列（表をパーティション化(※)するためのキー列）は削除できない
・列に多くのデータが含まれている場合は、削除に時間がかかる
※パーティション化とは大きな表や索引を複数の領域に分割して管理する機能です。パーティション単位でデータにアクセスできるため、パフォーマンスや管理上のメリットがあります。表をパーティション化するには、表の作成時にパーティション化するためのキー列を指定して、キー列の値の範囲などによって表を分割します。
列の削除中は表にロックがかかり、列に多くのデータが含まれている場合は削除に時間がかかります。多くのユーザーがデータベースを利用する時間帯に負荷の高い削除処理を行いたくない場合、削除したい列にUNUSEDマークを設定して未使用にできます。
ALTER TABLE 表名 SET UNUSED ( 列名 [, 列名...]) [CASCADE CONSTRAINTS];
※1つの列のみUNUSEDにする場合は、次の構文も使用できます。
ALTER TABLE 表名 SET UNUSED COLUMN 列名 [CASCADE CONSTRAINTS];
UNUSEDに設定した列は、次のALTER TABLE文で削除します。データベースの負荷の低い時間帯に行えます。
ALTER TABLE 表名 DROP UNUSED COLUMNS;
SET UNUSED句に関しては、以下の注意事項があります。
・UNUSEDに設定した列は戻せず、DESCRIBEコマンドなどで列名やデータ型を確認できなくなる
・UNUSEDにした列に作成された索引や制約は削除される
・UNUSEDにした列と同じ名前の列を表に追加できる
・UNUSEDにした列の表名と列数は「USER_UNUSED_COL_TABS」ディクショナリで確認できる
・表に対して作成したシノニムの再作成は必要ない
・UNUSEDにした列を含むビューは無効になる
・パーティション化キー列はUNUSEDにできない
・UNUSEDにした列はALTER TABLE ... DROP UNUSED COLUMNSコマンドで削除するまでは、表の上限の列数1000に含まれディスク領域も解放されない
【表のモード変更】
表には読取り/書込みモードと読取り専用モードの2つの状態があります。
表のモードの変更は次のように行います。
ALTER TABLE 表名 {READ WRITE | READ ONLY};
読取り専用モードの表にはデータの追加、更新、削除などのDML文は実行できませんが、表の削除や未使用の列の削除などデータを更新しないDDL文は実行できます。また、表に対する索引の操作も行えます。
・新しい列の追加
・既存の列のデータ型の変更
・既存の列へのデフォルト値の設定
・既存の列への制約の定義
・既存の列の削除
・既存の列名の変更
・表のモード変更(読み取り/書き込みモード、読み取り専用モード)
【新しい列の追加】
表への新しい列の追加は次のように行います。
ALTER TABLE 表名 ADD
( 列名 データ型(サイズ) [DEFAULT 値] [[CONSTRAINT 制約名] 制約タイプ]
[, 列名 データ型(サイズ)] ...
);
以下は既存のPROD表にNOTES列を追加する例です。表の最後に新しい列が追加されます。
SQLを表示
DESC prod;
ALTER TABLE prod ADD
(NOTES VARCHAR2(20)
);
DESC prod;
ALTER TABLE prod ADD
(NOTES VARCHAR2(20)
);
DESC prod;
表に既存のデータが存在する場合、新しく追加された列にはデフォルトでNULL値が設定されます。表が空でない場合、NOT NULL制約を定義した列を追加しようとするとエラーとなりますが、列の追加時にNULL以外のデフォルト値を指定することで、既存のデータの列にもデフォルト値が設定されます。
以下は、既存のデータが存在するPROD3表にNOT NULL制約を定義したSTATUS列を追加する例です。列の追加時にデフォルト値を指定しないとエラーとなりますが、デフォルト値を指定すると列を追加できます。
SQLを表示
ALTER TABLE prod3
ADD status VARCHAR2(20) NOT NULL;
ALTER TABLE prod3
ADD status VARCHAR2(20) DEFAULT 'FOR SALE' NOT NULL;
DESC prod3;
SELECT * FROM prod3;
ADD status VARCHAR2(20) NOT NULL;
ALTER TABLE prod3
ADD status VARCHAR2(20) DEFAULT 'FOR SALE' NOT NULL;
DESC prod3;
SELECT * FROM prod3;
【既存の列のデータ型、サイズ、デフォルト値の変更】
ALTER TABLE文で、既存の列のデータ型、サイズ、デフォルト値を変更できます。
ALTER TABLE 表名 MODIFY ( 列名 [データ型(サイズ)] [DEFAULT 値] [, 列名 ...]);
列の変更に関しては、以下のガイドラインがあります。
・列にNULL値のみが格納されている場合、データ型を変更できる
・列にデータが含まれている場合、列の変更後も格納できるデータ型やサイズであれば変更できる
[OK例] CHAR(10) → VARCHAR2(20), VARCHAR2(20)に10バイト以下の文字が格納されている → CHAR(10)
[NG例] CHAR(20)に10バイト以下の文字が格納されている → VARCHAR2(10)
※CHAR型は固定長のため「10バイト以下の文字 + 空白 = 20バイト」で格納される
・デフォルト値の変更は、新しい行の挿入時に反映される
【既存の列への制約の定義】
ALTER TABLE文で、既存の列へ制約を定義できます。
ALTER TABLE 表名 ADD [CONSTRAINT 制約名] 制約タイプ (列名);
ALTER TABLE 表名 MODIFY (列名 [CONSTRAINT 制約名] 制約タイプ);
但し、NOT NULL制約はALTER TABLE 文のADD CONSTRAINT句では追加できません。NOT NULL制約はMODIFY句で追加する必要があります。
また、ALTER TABLE文で既存の表の制約を無効化、もしくは有効化できます。
ALTER TABLE 表名 DISABLE CONSTRAINT 制約名 [CASCADE];
ALTER TABLE 表名 ENABLE CONSTRAINT 制約名 [CASCADE];
FOREIGN KEY制約から参照されているPRIMARY KEY制約またはUNIQUE制約をDISABLE CONSTRAINTで無効化する場合は、CASCADEオプションを指定して、無効化する制約を参照するFOREIGN KEY制約も同時に無効化しなければなりません。ENABLE CONSTRAINTで制約を有効化する場合はCASCADEオプションは指定できないので、各制約をそれぞれ有効化する必要があります。
【既存の列の削除】
既存の表の列の削除は次のように行います。
ALTER TABLE 表名 DROP ( 列名 [, 列名...]) [CASCADE CONSTRAINTS];
※1つの列のみ削除する場合は、次の構文も使用できます。
ALTER TABLE 表名 DROP COLUMN 列名 [CASCADE CONSTRAINTS];
列の削除に関しては、以下の注意事項があります。
・削除した列は戻せない
・表には最低1つの列を残す必要がある
・他から参照されるFOREIGN KEY制約の親表の列はCASCADE CONSTRAINTSオプションを指定する必要がある
・パーティション化キー列（表をパーティション化(※)するためのキー列）は削除できない
・列に多くのデータが含まれている場合は、削除に時間がかかる
※パーティション化とは大きな表や索引を複数の領域に分割して管理する機能です。パーティション単位でデータにアクセスできるため、パフォーマンスや管理上のメリットがあります。表をパーティション化するには、表の作成時にパーティション化するためのキー列を指定して、キー列の値の範囲などによって表を分割します。
列の削除中は表にロックがかかり、列に多くのデータが含まれている場合は削除に時間がかかります。多くのユーザーがデータベースを利用する時間帯に負荷の高い削除処理を行いたくない場合、削除したい列にUNUSEDマークを設定して未使用にできます。
ALTER TABLE 表名 SET UNUSED ( 列名 [, 列名...]) [CASCADE CONSTRAINTS];
※1つの列のみUNUSEDにする場合は、次の構文も使用できます。
ALTER TABLE 表名 SET UNUSED COLUMN 列名 [CASCADE CONSTRAINTS];
UNUSEDに設定した列は、次のALTER TABLE文で削除します。データベースの負荷の低い時間帯に行えます。
ALTER TABLE 表名 DROP UNUSED COLUMNS;
SET UNUSED句に関しては、以下の注意事項があります。
・UNUSEDに設定した列は戻せず、DESCRIBEコマンドなどで列名やデータ型を確認できなくなる
・UNUSEDにした列に作成された索引や制約は削除される
・UNUSEDにした列と同じ名前の列を表に追加できる
・UNUSEDにした列の表名と列数は「USER_UNUSED_COL_TABS」ディクショナリで確認できる
・表に対して作成したシノニムの再作成は必要ない
・UNUSEDにした列を含むビューは無効になる
・パーティション化キー列はUNUSEDにできない
・UNUSEDにした列はALTER TABLE ... DROP UNUSED COLUMNSコマンドで削除するまでは、表の上限の列数1000に含まれディスク領域も解放されない
【表のモード変更】
表には読取り/書込みモードと読取り専用モードの2つの状態があります。
表のモードの変更は次のように行います。
ALTER TABLE 表名 {READ WRITE | READ ONLY};
SQLを表示
ALTER TABLE employees2 READ ONLY;
INSERT INTO employees2
VALUES (3, 'Yamada', '1999-01-31');
INSERT INTO employees2
VALUES (3, 'Yamada', '1999-01-31');
読取り専用モードの表にはデータの追加、更新、削除などのDML文は実行できませんが、表の削除や未使用の列の削除などデータを更新しないDDL文は実行できます。また、表に対する索引の操作も行えます。
SQLを表示
ALTER TABLE employees2 READ WRITE;
INSERT INTO employees2
VALUES (3, 'Yamada', '1999-01-31');
INSERT INTO employees2
VALUES (3, 'Yamada', '1999-01-31');
主キー列 DROP の可否と解説の整合性について確認したいです
投稿日 2025/12/19
選択肢 ALTER TABLE child DROP (id);についてです。
解答及び解説では、正常に実行できるとされていますが、
child.id は 子表 childの PRIMARY KEY のため、制約を DROP せずに列を削除できずエラーとなるのではないでしょうか？
解答・解説が誤っているのか、あるいは私の理解に不足があるのか、を確認したく、投稿しました。
この問題の「正解は2つ」という解説は正しいのでしょうか？
もし正しい場合、ALTER TABLE child DROP(id); が成功する理由を教えていただきたいです。
よろしくお願いいたします。
r
rink_rew
2025/12/23 21:30
実際に試してみるとわかりますが、エラーにならずに削除できます。
SQL> CREATE TABLE parent
2 (
3 id NUMBER(2) CONSTRAINT pid_pk PRIMARY KEY,
4 dept_name VARCHAR2(10)
5 );
表が作成されました。
SQL> CREATE TABLE child
2 (
3 id NUMBER(2) CONSTRAINT cid_pk PRIMARY KEY,
4 name VARCHAR2(10) CONSTRAINT cname_uq UNIQUE,
5 deptid NUMBER(2) CONSTRAINT dept_fk REFERENCES parent (id) ON DELETE CASCADE
6 );
表が作成されました。
SQL> INSERT INTO parent VALUES (1, 'parent');
1行が作成されました。
SQL> INSERT INTO child VALUES (10, 'child', 1);
1行が作成されました。
SQL> ALTER TABLE child DROP (id);
表が変更されました。
SQL> desc child
名前 NULL? 型
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
DEPTID NUMBER(2)
SQL>
たとえば、列pkを他の表から参照する参照制約が他に存在しない場合は、次の文を指定するときにCASCADE CONSTRAINTS句がなくてもエラーにはなりません。
コメント
この投稿に対して返信しませんか？
f fjn_inagaki
2025/12/24 10:20
ご回答ありがとうございます。主キー制約がついている場合、その列は必ず削除できないものだと認識しておりましたが、勘違いだったようですね。認識を改めておきます。ありがとうございました。