WITH CHECK OPTION

テーブルがAUTO_INCREMENTカラムを含んでいないテーブル上にある挿入可能なビューに
挿入するAUTO_INCREMENTカラムを含んでいる場合、ビューの一部でないカラムにデフォルト
値を挿入した副作用が見えないので、カラムはLAST_INSERT_ID()の値を変更しません。

それに対するselect_statement中のWHERE節が真実であるものを除く行に、
更新不能なビューが挿入されるか、当該行が更新されるのを回避するため、
WITH CHECK OPTION節を附与することができます。

更新可能なビューに対するWITH CHECK OPTION節に基づき、LOCALとCASCADEDキーワード
はビューが他のビューに対して定義される場合、チェックテストの範囲を決めます。
定義されているビューだけに対して、LOCALキーワードはCHECK OPTIONを制限します。
CASCADEDは同様に基礎ビューを評価するチェックを起動させます。キーワードが附与されない場合、
デフォルト設定はCASCADEDとなります。以下のテーブル並びにビューのセットを考慮すると:

CREATE TABLE TABLE01 (ID INT);


CREATE VIEW VIEW01 AS SELECT * FROM TABLE01 WHERE ID < 2 WITH CHECK OPTION;


CREATE VIEW VIEW02 AS SELECT * FROM VIEW01 WHERE ID > 0
WITH LOCAL CHECK OPTION;


CREATE VIEW VIEW03 AS SELECT * FROM VIEW01 WHERE ID > 0
WITH CASCADED CHECK OPTION;

create_view_check_opt

v2ビューとv3ビューが他のビューに対して定義され、v1. v2 にはLOCAL チェックオプションが含まれています。
従って、挿入はv2チェックだけに対してテストされます。v3にはCASCADED チェックオプションが含まれているので、
挿入は、自身のチェックのみならず、基礎ビューに対してもテストされます。

WITH LOCAL CHECK OPTION = ( ID > 0 )
INSERT INTO VIEW02 VALUES (2);

WITH CASCADED CHECK OPTION = (ID > 0 と ID < 2 )
INSERT INTO VIEW03 VALUES (2);

--------------------------------------------------------------------------------------
VIEW03にはCASCADEオプションでVIEW03のCHECK OPTIONも適用された。
--------------------------------------------------------------------------------------
mysql> INSERT INTO VIEW01 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'STUDY.VIEW01'
mysql>
-------------------------------------------

view_cascade

関連システム変数

updatable_views_with_limit

更新の許可するかどうかを制御する。ビューに基準テーブルで定義したプライマリキー
のすべてのカラムが含まれていない場合に、更新ステートメントで LIMIT 節を含んでいたら、
そのビューを更新するかどうか、ということである。このような更新は GUI ツールなどから生成される。
ここでの更新は UPDATE または DELETE ステートメントのこと。ここでのプライマリ キーとは
PRIMARY KEY または UNIQUE インデックスのことで、NULL をカラムに含まない。

この変数の値は 2 種類ある。

* 1 または YES:エラー メッセージではなく、警告だけを発行。(デフォルト値)
* 0 または NO:更新禁止。

参考サイト


20.2. CREATE VIEW 構文

4.2.3. システム変数


幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、
UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。
ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在
しなければなりません。ビューを更新不能にするその他の生成子もあります。
もっと具体的に言うと、それが以下のいずれかを含んでいるとビューは更新可能となりません。

* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
* DISTINCT
* GROUP BY
* HAVING
* UNION もしくはUNION ALL
* 選択リスト中のサブ・クエリ
* 特定結合(このセクション中の後の部分に追加した結合の説明参照)
* FROM節中の更新不能ビュー
* FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ
* 文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)
* ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)

① [更新不可能 ] Use of ALGORITHM=TEMPTABLE in the view definition

TEMPTABLEの場合、ビューの結果がテンポラリーテーブルの中に復元され、
その後、ステートメントを実行するために使用されます。
よってUPDATE文は、TEMPTABLEに影響するがベーステーブルには影響しない。

② [更新可能 ]  Use of ALGORITHM=MERGE in the view definition

UNDEFINEDの場合、MySQLは使用すべきアルゴリズムを選択します。
それは出来るだけTEMPTABLEよりMERGEを優先します。これは、MERGEは通常より効率的で、
ビューはテンポラリテーブルを使用すると更新可能ではなくなるためです。

明確にTEMPTABLEを選択する理由は、テンポラリテーブルを選んだ後ステートメントの
処理終了に使用する前に、内在するテーブルのロックを解放することができるからです。
その結果、ロックをMERGEアルゴリズムよりも速やかに解除し、ビューを使う他のクライアント
が長時間ブロックされないようにします。

————————–
補足
————————–
以下に示す3つの理由によって、ビューアルゴリズムをUNDEFINEDにすることができます。
* CREATE VIEWステートメントの中にALGORITHM節が現れない。
* CREATE VIEWステートメントにALGORITHM = UNDEFINED節が明確に含まれている。
* テンポラリテーブルだけを使って処理できるビューに対して、ALGORITHM = MERGEが規定される。
この場合、MySQL は警告を発し、アルゴリズムをUNDEFINEDにセットします。

③  [更新不可能 ] Use of aggregate functions in the view definition

④ [更新不可能 ] Use of GROUP BY or HAVING clauses in the view definition

⑤ [更新可能 ] Use of expressions like col = col + 1 in the view definition


mysql> UPDATE V_CountryPopCalc SET PopNew = 1000
-> WHERE Name = 'United States';
ERROR 1348 (HY000): Column 'PopNew' is not updatable


mysql> UPDATE V_CountryPopCalc SET Name = 'Update Test for V_CountryPopCalc'
-> WHERE Name = 'United States';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

update

参考サイト

20.2. CREATE VIEW 構文