Open Source RDBMS - Seamless, Scalable, Stable and Free

English | 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
문서 정보
viewed 4424 times
번역 en
작성자
posted 2년 전
CUBRID
공헌자
마지막 수정시간 2년 전
변경 내역 보기
Share this article