<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="alter-table-examples">
      </a>
      15.1.9.3 ALTER TABLE Examples
     </h4>
    </div>
   </div>
  </div>
  <p>
   Begin with a table
   <code class="literal">
    t1
   </code>
   created as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa22194175"><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">INTEGER</span><span class="token punctuation">,</span> b <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To rename the table from
   <code class="literal">
    t1
   </code>
   to
   <code class="literal">
    t2
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa3933792"><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">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">RENAME</span> t2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To change column
   <code class="literal">
    a
   </code>
   from
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INTEGER
    </code>
   </a>
   to
   <code class="literal">
    TINYINT NOT
        NULL
   </code>
   (leaving the name the same), and to change column
   <code class="literal">
    b
   </code>
   from
   <code class="literal">
    CHAR(10)
   </code>
   to
   <code class="literal">
    CHAR(20)
   </code>
   as well as renaming it from
   <code class="literal">
    b
   </code>
   to
   <code class="literal">
    c
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa74948819"><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">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">MODIFY</span> a <span class="token datatype">TINYINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token keyword">CHANGE</span> b c <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To add a new
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column
        named
   <code class="literal">
    d
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa5282018"><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">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ADD</span> d <span class="token datatype">TIMESTAMP</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To add an index on column
   <code class="literal">
    d
   </code>
   and a
   <code class="literal">
    UNIQUE
   </code>
   index on column
   <code class="literal">
    a
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa34594166"><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">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>d<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ADD</span> <span class="token keyword">UNIQUE</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To remove column
   <code class="literal">
    c
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90927908"><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">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> c<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To add a new
   <code class="literal">
    AUTO_INCREMENT
   </code>
   integer column
        named
   <code class="literal">
    c
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14857502"><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">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ADD</span> c <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
  <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>c<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   We indexed
   <code class="literal">
    c
   </code>
   (as a
   <code class="literal">
    PRIMARY
        KEY
   </code>
   ) because
   <code class="literal">
    AUTO_INCREMENT
   </code>
   columns
        must be indexed, and we declare
   <code class="literal">
    c
   </code>
   as
   <code class="literal">
    NOT NULL
   </code>
   because primary key columns cannot
        be
   <code class="literal">
    NULL
   </code>
   .
  </p>
  <p>
   For
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   tables, it is also possible
        to change the storage type used for a table or column. For
        example, consider an
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   table
        created as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14612817"><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">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 keyword">TABLESPACE</span> ts_1 <span class="token keyword">ENGINE</span> <span class="token keyword">NDB</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (1.27 sec)</span></code></pre>
  </div>
  <p>
   To convert this table to disk-based storage, you can use the
        following
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18317559"><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">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">TABLESPACE</span> ts_1 <span class="token keyword">STORAGE</span> <span class="token keyword">DISK</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (2.99 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> t1
Create Table<span class="token punctuation">:</span> CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /<span class="token punctuation">*</span>!50100 TABLESPACE ts_1 STORAGE DISK <span class="token punctuation">*</span>/
ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span><span class="token output">1 row in set (0.01 sec)</span></code></pre>
  </div>
  <p>
   It is not necessary that the tablespace was referenced when the
        table was originally created; however, the tablespace must be
        referenced by the
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa41171675"><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">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span> ts_1 <span class="token keyword">ENGINE</span> <span class="token keyword">NDB</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (1.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">STORAGE</span> <span class="token keyword">DISK</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1005 (HY000)<span class="token punctuation">:</span> Can't create table 'c.#sql-1750_3' (errno<span class="token punctuation">:</span> 140)</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">TABLESPACE</span> ts_1 <span class="token keyword">STORAGE</span> <span class="token keyword">DISK</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (3.42 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> t1
Create Table<span class="token punctuation">:</span> CREATE TABLE `t2` (
  `c1` int(11) DEFAULT NULL
) /<span class="token punctuation">*</span>!50100 TABLESPACE ts_1 STORAGE DISK <span class="token punctuation">*</span>/
ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span><span class="token output">1 row in set (0.01 sec)</span></code></pre>
  </div>
  <p>
   To change the storage type of an individual column, you can use
   <code class="literal">
    ALTER TABLE ... MODIFY [COLUMN]
   </code>
   . For example,
        suppose you create an NDB Cluster Disk Data table with two
        columns, using this
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84584268"><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">CREATE</span> <span class="token keyword">TABLE</span> t3 <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 prompt">    -&gt;</span>     <span class="token keyword">TABLESPACE</span> ts_1 <span class="token keyword">STORAGE</span> <span class="token keyword">DISK</span> <span class="token keyword">ENGINE</span> <span class="token keyword">NDB</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (1.34 sec)</span></code></pre>
  </div>
  <p>
   To change column
   <code class="literal">
    c2
   </code>
   from disk-based to
        in-memory storage, include a
   <code class="literal">
    STORAGE MEMORY
   </code>
   clause in the column definition used by the
   <code class="literal">
    ALTER
        TABLE
   </code>
   statement, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31028319"><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">ALTER</span> <span class="token keyword">TABLE</span> t3 <span class="token keyword">MODIFY</span> c2 <span class="token datatype">INT</span> <span class="token keyword">STORAGE</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (3.14 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   You can make an in-memory column into a disk-based column by
        using
   <code class="literal">
    STORAGE DISK
   </code>
   in a similar fashion.
  </p>
  <p>
   Column
   <code class="literal">
    c1
   </code>
   uses disk-based storage, since this
        is the default for the table (determined by the table-level
   <code class="literal">
    STORAGE DISK
   </code>
   clause in the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement). However,
        column
   <code class="literal">
    c2
   </code>
   uses in-memory storage, as can be
        seen here in the output of
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE
        TABLE
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26410411"><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">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> t3
Create Table<span class="token punctuation">:</span> CREATE TABLE `t3` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) /<span class="token punctuation">*</span>!50120 STORAGE MEMORY <span class="token punctuation">*</span>/ DEFAULT NULL
) /<span class="token punctuation">*</span>!50100 TABLESPACE ts_1 STORAGE DISK <span class="token punctuation">*</span>/ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span><span class="token output">1 row in set (0.02 sec)</span></code></pre>
  </div>
  <p>
   When you add an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column, column
        values are filled in with sequence numbers automatically. For
   <code class="literal">
    MyISAM
   </code>
   tables, you can set the first sequence
        number by executing
   <code class="literal">
    SET
        INSERT_ID=
    <em class="replaceable">
     <code>
      value
     </code>
    </em>
   </code>
   before
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   or by using the
   <code class="literal">
    AUTO_INCREMENT=
    <em class="replaceable">
     <code>
      value
     </code>
    </em>
   </code>
   table option.
  </p>
  <p>
   With
   <code class="literal">
    MyISAM
   </code>
   tables, if you do not change the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column, the sequence number is
        not affected. If you drop an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column and then add another
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column, the numbers are resequenced beginning with 1.
  </p>
  <p>
   When replication is used, adding an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column to a table might not
        produce the same ordering of the rows on the replica and the
        source. This occurs because the order in which the rows are
        numbered depends on the specific storage engine used for the
        table and the order in which the rows were inserted. If it is
        important to have the same order on the source and replica, the
        rows must be ordered before assigning an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   number. Assuming that you want
        to add an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column to the table
   <code class="literal">
    t1
   </code>
   , the following statements produce a new
        table
   <code class="literal">
    t2
   </code>
   identical to
   <code class="literal">
    t1
   </code>
   but with an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50918407"><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> t2 <span class="token punctuation">(</span>id <span class="token datatype">INT</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> col1<span class="token punctuation">,</span> col2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This assumes that the table
   <code class="literal">
    t1
   </code>
   has columns
   <code class="literal">
    col1
   </code>
   and
   <code class="literal">
    col2
   </code>
   .
  </p>
  <p>
   This set of statements also produces a new table
   <code class="literal">
    t2
   </code>
   identical to
   <code class="literal">
    t1
   </code>
   , with
        the addition of an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89608731"><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> t2 <span class="token operator">LIKE</span> t1<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">ADD</span> id <span class="token datatype">INT</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> col1<span class="token punctuation">,</span> col2<span class="token punctuation">;</span></code></pre>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    To guarantee the same ordering on both source and replica,
    <span class="emphasis">
     <em>
      all
     </em>
    </span>
    columns of
    <code class="literal">
     t1
    </code>
    must
          be referenced in the
    <code class="literal">
     ORDER BY
    </code>
    clause.
   </p>
  </div>
  <p>
   Regardless of the method used to create and populate the copy
        having the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column, the final
        step is to drop the original table and then rename the copy:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36954513"><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">DROP</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">RENAME</span> t1<span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
