<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="view-check-option">
      </a>
      27.5.4 The View WITH CHECK OPTION Clause
     </h3>
    </div>
   </div>
  </div>
  <p>
   The
   <code class="literal">
    WITH CHECK OPTION
   </code>
   clause can be given for
      an updatable view to prevent inserts to rows for which the
   <code class="literal">
    WHERE
   </code>
   clause in the
   <em class="replaceable">
    <code>
     select_statement
    </code>
   </em>
   is not true. It also
      prevents updates to rows for which the
   <code class="literal">
    WHERE
   </code>
   clause is true but the update would cause it to be not true (in
      other words, it prevents visible rows from being updated to
      nonvisible rows).
  </p>
  <p>
   In a
   <code class="literal">
    WITH CHECK OPTION
   </code>
   clause for an updatable
      view, the
   <code class="literal">
    LOCAL
   </code>
   and
   <code class="literal">
    CASCADED
   </code>
   keywords determine the scope of check testing when the view is
      defined in terms of another view. When neither keyword is given,
      the default is
   <code class="literal">
    CASCADED
   </code>
   .
  </p>
  <p>
   <code class="literal">
    WITH CHECK OPTION
   </code>
   testing is
      standard-compliant:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      With
      <code class="literal">
       LOCAL
      </code>
      , the view
      <code class="literal">
       WHERE
      </code>
      clause is checked, then checking
          recurses to underlying views and applies the same rules.
     </p>
    </li>
    <li class="listitem">
     <p>
      With
      <code class="literal">
       CASCADED
      </code>
      , the view
      <code class="literal">
       WHERE
      </code>
      clause is checked, then checking
          recurses to underlying views, adds
      <code class="literal">
       WITH CASCADED
          CHECK OPTION
      </code>
      to them (for purposes of the check;
          their definitions remain unchanged), and applies the same
          rules.
     </p>
    </li>
    <li class="listitem">
     <p>
      With no check option, the view
      <code class="literal">
       WHERE
      </code>
      clause
          is not checked, then checking recurses to underlying views,
          and applies the same rules.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Consider the definitions for the following table and set of views:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47926482"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> v1 <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> a <span class="token operator">&lt;</span> <span class="token number">2</span>
<span class="token keyword">WITH</span> <span class="token keyword">CHECK</span> <span class="token keyword">OPTION</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> v2 <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> v1 <span class="token keyword">WHERE</span> a <span class="token operator">&gt;</span> <span class="token number">0</span>
<span class="token keyword">WITH</span> <span class="token keyword">LOCAL</span> <span class="token keyword">CHECK</span> <span class="token keyword">OPTION</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> v3 <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> v1 <span class="token keyword">WHERE</span> a <span class="token operator">&gt;</span> <span class="token number">0</span>
<span class="token keyword">WITH</span> <span class="token keyword">CASCADED</span> <span class="token keyword">CHECK</span> <span class="token keyword">OPTION</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Here the
   <code class="literal">
    v2
   </code>
   and
   <code class="literal">
    v3
   </code>
   views are
      defined in terms of another view,
   <code class="literal">
    v1
   </code>
   .
  </p>
  <p>
   Inserts for
   <code class="literal">
    v2
   </code>
   are checked against its
   <code class="literal">
    LOCAL
   </code>
   check option, then the check recurses to
   <code class="literal">
    v1
   </code>
   and the rules are applied again. The rules
      for
   <code class="literal">
    v1
   </code>
   cause a check failure. The check for
   <code class="literal">
    v3
   </code>
   also fails:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22632290"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> v2 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1369 (HY000)<span class="token punctuation">:</span> CHECK OPTION failed 'test.v2'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> v3 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1369 (HY000)<span class="token punctuation">:</span> CHECK OPTION failed 'test.v3'</span></code></pre>
  </div>
 </div>
 <br/>
</div>
