Working with AUTO_INCREMENT Columns in MySQL

MySQLMySQLi Database

<p>Let us understand how to work with AUTO_INCREMENT columns in MySQL &minus;</p><p>The AUTO_INCREMENT attribute is used to generate a unique identify for new rows. Let us see how this statement works. Before that, consider the below query &minus;</p><h2>Query</h2><pre class="result notranslate" style="">CREATE TABLE tableName ( &nbsp; &nbsp;id MEDIUMINT NOT NULL AUTO_INCREMENT, &nbsp; &nbsp;name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO tableName (name) VALUES (&lsquo;val1&rsquo;),(&#39;val2&#39;),(&#39;val3&#39;), (&#39;val4&#39;); SELECT * FROM tableName;</pre><h2>Output</h2><pre class="result notranslate">+----+---------+ | id | name | +----+---------+ | 1 | val1 | | 2 | val2 | | 3 | val3 | | 4 | val4 | +----+---------+</pre><p>In the above query, no value was specified for the &lsquo;AUTO_INCREMENT&rsquo; column, hence MySQL assigned a sequence of numbers automatically to the &lsquo;id&rsquo; column. A value of 0 can also be explicitly assigned so that the number sequence begins from 0. This can be done only if &lsquo;NO AUTO VALUE ON ZERO&rsquo; SQL mode is not enabled.</p><p>If a column is declared as &lsquo;NOT NULL&rsquo;, it is possible to assign NULL to that column to generate a sequence of numbers.</p><p>When any value is inserted into an AUTO_INCREMENT column, the column gets set to that value, and the sequence also gets reset so that it generates values automatically, in the sequential range from largest column value.</p><p>An existing &lsquo;AUTO_INCREMENT&rsquo; column can be updated that will reset the &lsquo;AUTO_INCREMENT&rsquo; sequence as well. The most recent auto-generated &lsquo;AUTO_INCREMENT; value can be retrieved using the &lsquo;LAST_INSERT_ID()&rsquo; function in SQL or using the &lsquo;mysql_insert_id()&rsquo; which is a C API function.</p><p>The following rules need to be followed while using the AUTO_INCREMENT attribute &minus;</p><ul class="list"><li><p>Every table has only one AUTO_INCREMENT column whose data type would be an integer typically.</p></li><li><p>The AUTO_INCREMENT column needs to be indexed. This means it can either be a PRIMARY KEY or a UNIQUE index.</p></li><li><p>The AUTO_INCREMENT column must have a NOT NULL constraint on it.</p></li><li><p>When the AUTO_INCREMENT attribute is set to a column, MySQL automatically adds the NOT NULL constraint to the column on its own.</p></li></ul>
Updated on 09-Mar-2021 13:34:27