SQL INSERT and auto increment

What is an automatic increment?

Some tables are configured in such a way that each time you add a row, one of the columns is automatically filled in.
This column takes a value greater than the previous column. Example: first row gets the value 1, second row the value 2, … As a result, two rows cannot have the same value in this column.

What is the purpose of never having two rows with the same value in this column?

Such a column can be used to store each row’s unique identifier. A unique identifier is like a social security number: everybody owns a unique social security number. Nobody else can have the same security number as you.

Why having the increment filled in automatically?

In that case, you do not have to care, when inserting a row, about assigning to this column a unique value.

What is the syntax:

If you want to insert a row in the clients table, which has the following columns:

  • id (uses an auto increment).
  • first_name.
  • last_name.
  • city.
  • account_type.

To insert such a row, the syntax is:

insert into clients (first_name, last_name, city, account_type)
values (
'Ankur',
'Malhotra',
'Mumbai',
'key_account')

Note that:

  • we do not include the column with auto increment in the list of columns.
  • we do not write the value of the auto increment in the insert statement: it will be calculated automatically during the insertion.
  • the values provided after the keyword “values” are corresponding to columns indicated in parenthesis in the first line (first_name, last_name, city, account_type).
  • you write these values in the same order as the columns in parenthesis.
I invite you to go to the next chapter about the SQL DELETE FROM command.
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: September 18, 2014