<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="alter-table-generated-columns">
      </a>
      15.1.9.2 ALTER TABLE and Generated Columns
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045188596736">
  </a>
  <p>
   <code class="literal">
    ALTER TABLE
   </code>
   operations permitted for
        generated columns are
   <code class="literal">
    ADD
   </code>
   ,
   <code class="literal">
    MODIFY
   </code>
   , and
   <code class="literal">
    CHANGE
   </code>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Generated columns can be added.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71258805"><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>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The data type and expression of generated columns can be
            modified.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa191251"><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>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> c2 <span class="token datatype">TINYINT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">5</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Generated columns can be renamed or dropped, if no other
            column refers to them.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40861212"><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>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHANGE</span> c2 c3 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> c3<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Virtual generated columns cannot be altered to stored
            generated columns, or vice versa. To work around this, drop
            the column, then add it with the new definition.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17759601"><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>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">VIRTUAL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> c2<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Nongenerated columns can be altered to stored but not
            virtual generated columns.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11934309"><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>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Stored but not virtual generated columns can be altered to
            nongenerated columns. The stored generated values become the
            values of the nongenerated column.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19303787"><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>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> c2 <span class="token datatype">INT</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ADD COLUMN
      </code>
      is not an in-place operation
            for stored columns (done without using a temporary table)
            because the expression must be evaluated by the server. For
            stored columns, indexing changes are done in place, and
            expression changes are not done in place. Changes to column
            comments are done in place.
     </p>
    </li>
    <li class="listitem">
     <p>
      For non-partitioned tables,
      <code class="literal">
       ADD COLUMN
      </code>
      and
      <code class="literal">
       DROP COLUMN
      </code>
      are in-place operations
            for virtual columns. However, adding or dropping a virtual
            column cannot be performed in place in combination with
            other
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      operations.
     </p>
     <p>
      For partitioned tables,
      <code class="literal">
       ADD COLUMN
      </code>
      and
      <code class="literal">
       DROP COLUMN
      </code>
      are not in-place operations
            for virtual columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       InnoDB
      </code>
      supports secondary indexes on
            virtual generated columns. Adding or dropping a secondary
            index on a virtual generated column is an in-place
            operation. For more information, see
      <a class="xref" href="create-table-secondary-indexes.html" title="15.1.20.9 Secondary Indexes and Generated Columns">
       Section 15.1.20.9, “Secondary Indexes and Generated Columns”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When a
      <code class="literal">
       VIRTUAL
      </code>
      generated column is added
            to a table or modified, it is not ensured that data being
            calculated by the generated column expression is not out of
            range for the column. This can lead to inconsistent data
            being returned and unexpectedly failed statements. To permit
            control over whether validation occurs for such columns,
      <code class="literal">
       ALTER TABLE
      </code>
      supports
      <code class="literal">
       WITHOUT
            VALIDATION
      </code>
      and
      <code class="literal">
       WITH VALIDATION
      </code>
      clauses:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         With
         <code class="literal">
          WITHOUT VALIDATION
         </code>
         (the default
                if neither clause is specified), an in-place operation
                is performed (if possible), data integrity is not
                checked, and the statement finishes more quickly.
                However, later reads from the table might report
                warnings or errors for the column if values are out of
                range.
        </p>
       </li>
       <li class="listitem">
        <p>
         With
         <code class="literal">
          WITH VALIDATION
         </code>
         ,
         <code class="literal">
          ALTER
                TABLE
         </code>
         copies the table. If an out-of-range or
                any other error occurs, the statement fails. Because a
                table copy is performed, the statement takes longer.
        </p>
       </li>
      </ul>
     </div>
     <p>
      <code class="literal">
       WITHOUT VALIDATION
      </code>
      and
      <code class="literal">
       WITH
            VALIDATION
      </code>
      are permitted only with
      <code class="literal">
       ADD
            COLUMN
      </code>
      ,
      <code class="literal">
       CHANGE COLUMN
      </code>
      , and
      <code class="literal">
       MODIFY COLUMN
      </code>
      operations. Otherwise, an
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_wrong_usage" target="_top">
       <code class="literal">
        ER_WRONG_USAGE
       </code>
      </a>
      error occurs.
     </p>
    </li>
    <li class="listitem">
     <p>
      If expression evaluation causes truncation or provides
            incorrect input to a function, the
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement
            terminates with an error and the DDL operation is rejected.
     </p>
    </li>
    <li class="listitem">
     <p>
      An
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement that
            changes the default value of a column
      <em class="replaceable">
       <code>
        col_name
       </code>
      </em>
      may also change the
            value of a generated column expression that refers to the
            column using
      <em class="replaceable">
       <code>
        col_name
       </code>
      </em>
      , which may
            change the value of a generated column expression that
            refers to the column using
      <a class="link" href="miscellaneous-functions.html#function_default">
       <code class="literal">
        DEFAULT(
        <em class="replaceable">
         <code>
          col_name
         </code>
        </em>
        )
       </code>
      </a>
      .
            For this reason,
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      operations that change the definition of a column cause a
            table rebuild if any generated column expression uses
      <a class="link" href="miscellaneous-functions.html#function_default">
       <code class="literal">
        DEFAULT()
       </code>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
