Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

How to Insert Data Into Tables with AUTO_INCREMENT Column(s)


If you want to know how to create a table with an AUTO_INCREMENT attribute on a field, you can check out the Column Definition and AUTO_INCREMENT Clause manual pages.

The purpose of this tutorial is to explain the 2 methods that can be used to insert values within a table that has an AUTO_INCREMENT column.

Let's assume we have the following table with one id field that has an AUTO_INCREMENT field and a VARCHAR name field:

CREATE TABLE auto_tbl(id INT AUTO_INCREMENT, name VARCHAR);

You can insert values in this table by either specifying the column name (id) and putting the value NULL (or null) in the values section or by omitting the id column completely when specifying the column names that will be inserted.

INSERT INTO auto_tbl VALUES (NULL, 'AAA');
INSERT INTO auto_tbl (id, name) VALUES (NULL, 'AAA');
INSERT INTO auto_tbl (name) VALUES ('DDD');

Optionally, you can insert multiple sets of values by just separating them with ",". As you can see, the same above rules for the AUTO_INCREMENT column apply.

INSERT INTO auto_tbl VALUES (NULL, 'AAA'),(NULL, 'BBB'),(NULL, 'CCC');
INSERT INTO auto_tbl (id, name) VALUES (NULL, 'AAA'),(NULL, 'BBB'),(NULL, 'CCC');
INSERT INTO auto_tbl (name) VALUES ('DDD'),('EEE');

Important! If you don't set the value to null or NULL and insert values manually, then the AUTO_INCREMENT seed is not updated. This may lead to unique constraint violation errors if the AUTO_INCREMENT column has an UNIQUE constraint or is a PRIMARY KEY. For more information, check out Step 3 from Unique Constraint Violation Error.

comments powered by Disqus
Page info
viewed 4443 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 2 years ago by
View revisions
Share this article