助け合いフォーラム
次のSQL文でEMPLOYEES表を実表とするV_EMPビューを作成しました。
CREATE OR REPLACE VIEW v_emp
AS
SELECT department_id, employee_id, employee_name, salary
FROM employees
WHERE salary > 250000 AND salary < 800000
AND department_id = 1
WITH CHECK OPTION;
V_EMPビューに対して、エラーとならずに実行できるSQL文はどれですか(該当するものを全て選択して下さい)。
正解
INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);
UPDATE v_emp SET salary = 500000 WHERE department_id = 3;
解説
V_EMPビューは、
・SALARY列の値が250,000より大きく800,000より小さい
・DEPARTMENT_ID列の値が1
という条件が指定されているので、この条件を満たすデータの追加、更新のみ行うことができます。
以上より、
・INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);
・UPDATE v_emp SET salary = 500000 WHERE department_id = 3;
が正解となります。
後者のSQL文は、WHERE句でdepartment_id = 3という条件が指定されていますが、更新する値(salary = 500000)自体はビューで定義された条件を満たしているためエラーとはなりませんので注意しましょう。
ただし、V_EMPビューではDEPARTMENT_ID列が1のデータにしかアクセス出来ないため、データの更新は行われません。
正解のSQL文の実行結果は次のようになります。
AS
SELECT department_id, employee_id, employee_name, salary
FROM employees
WHERE salary > 250000 AND salary < 800000
AND department_id = 1
WITH CHECK OPTION;
INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 600000);
WHERE department_id = 3;
その他の選択肢については次のとおりです。
・UPDATE v_emp SET salary = 1000000 WHERE department_id = 1;
・INSERT INTO v_emp VALUES (1, 10, 'Tanaka', 150000);
SALARY列の値がビューの定義を満たしていないため、エラーとなります。
・INSERT INTO v_emp VALUES (1, NULL, 'Tanaka', 40000);
EMPLOYEES表のEMPLOYEE_ID列にはNOT NULL制約が定義されているため、NULL値を登録することはできません。
参考
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW ビュー名 [(別名 [,別名...])]
AS
副問合せ
[WITH CHECK OPTION [CONSTRAINT 制約名]]
[WITH READ ONLY [CONSTRAINT 制約名]];
AS
SELECT employee_id, employee_name, hiredate, department_id
FROM employees
WHERE salary <= 450000;
SELECT * FROM v_emp;
CREATE VIEW文のオプションは次の通りです。
[FORCE/NOFORCE]
CREATE VIEW文でビューを作成する際に、FORCEオプションを指定すると、実表の有無にかかわらずビューを作成することができます。
メンテナンスや検証時に、一時的に実表が存在しないがビューを作成しておきたい場合などに利用します。
CREATE FORCE VIEW view1
AS
SELECT employee_id, employee_name, salary
FROM emp3;
ただし、ビューの参照時に実表が存在しない場合はエラーとなります。
NOFORCEオプションを指定すると、実表がある場合のみビューが作成され、実表がない場合はエラーとなります。
CREATE NOFORCE VIEW view1
AS
SELECT employee_id, employee_name, salary
FROM emp3;
なお、CREATE VIEW文ではNOFORCEがデフォルトとなりますので、FORCE、NOFORCEどちらも指定しない場合は、実表がある場合のみビューを作成することができます。
[WITH CHECK OPTION]
ビューの作成時、WITH CHECK OPTIONオプションを指定すると、ビューを通じて実表のデータを操作する場合に、ビューの定義時に指定したWHERE句の条件を満たしていないデータの追加や更新ができなくなります。
AS
SELECT employee_name, salary
FROM employees
WHERE department_id = 5
AND salary >= 400000
WITH CHECK OPTION;
UPDATE v_emp SET salary = 300000;
UPDATE v_emp SET salary = 600000;
CREATE VIEW文でビューを作成する場合は、次の点に注意が必要です。
・副問合せのSELECT句に計算式や関数を指定する場合は、CREATE VIEW文で別名を指定するか、SELECT句の計算式や関数に列別名を指定する
AS
SELECT employee_id, employee_name, salary*12, hiredate
FROM employees;
・ビューの列名を定義する場合は、副問合せのSELECT句に指定する列の数と同数の列名を定義する
AS
SELECT * FROM employees
WHERE salary >= 500000;
なお、ビューの構造は表と同様にDESCコマンドで確認することができます。
WITH CHECK OPTIONについて
後者のSQL文は、WHERE句でdepartment_id = 3という条件が指定されていますが、更新する値(salary = 500000)自体はビューで定義された条件を満たしているためエラーとはなりませんので注意しましょう。
という解説が腑に落ちません。
insertはdepartment_id = 1でないとエラーになるようですが、updataは条件以外のレコードでもよいということなのでしょうか。どのように判断すればよいのかわかりません。
insertはdepartment_id = 1でないとエラーになるようですが、updataは条件以外のレコードでもよいということなのでしょうか
問題のVIEWのポイントって
SELECT department_id, employee_id, employee_name, salary
FROM employees
WHERE salary > 250000 AND salary < 800000
AND department_id = 1
ですよね。要件としては
- 部署ID（
department_id）が1 - 給与（
salary）が250000から800000の間
です。
当たり前ですが、INSERTする時には「この条件に当てはまる行の挿入」となるので、どちらも満たさないと絶対エラーになりますよね。ですがUPDATEは「この条件に当てはまるものを指定した内容に更新」するよう指示しているだけです。改めて後者（UPDATEの方ですよね？）をみると
UPDATE v_emp SET salary = 500000 WHERE department_id = 3;
となっており、「部署IDが3の行のsalaryを一律500000にせよ」という指示なので、部署IDが3のものを見つけて更新しようとしますがVIEWの定義により部署ID:3のレコードはヒットしない（VIEWのWHERE句に含まれない）ので「対象レコードが見つからず更新できない＝実行した結果0件更新できたよ」となるだけでエラーにはならないというだけのことです。
INSERT時は範囲外の値をセットすると「範囲外の値は入力できない（エラー）」となりますが、UPDATE時は更新指示自体がエラーになるのではなく「処理を実行した結果0件でした」となるのだよ、という違いですね。
コメント
この投稿に対して返信しませんか？
m mmmss125
2023/01/23 22:03
ご丁寧にありがとうございます！ 理解することができました。