AUTO_INCREMENT vs. INSERT

philippo

In MySQL it is possible to insert NULL into a identifying column (AUTO_INCREMENT) in order to get the correct sequence number.
Does this also work on PostgreSQL, M$SQL, Oracle,... ?

e.g.
CREATE TABLE testtbl (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
url VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
}

Does this work on any DBS (SQL standard?) ?:

INSERT INTO testtbl VALUES (NULL, 'www.testing.com');

Or do I need to specify the column list?:

INSERT INTO testtbl (url) VALUES ('www.testing.com');

thx

Anon

There is no support for auto_increment in the SQL standard. Your code will only work with Mysql.

Actually, your create table statement will not work in Mysql either, as it is only integer columns that can have the auto_increment property.

philippo

Thanks for your hint.

Anyway, this CREATE TABLE works fine in MySQL. I've got an integer column with the AUTO_INCREMENT property:
id INT UNSIGNED NOT NULL AUTO_INCREMENT

What should be wrong with that? MySQL handles INT as INT(10)

Anon

Sorry, that's my mistake. I misread your post.

csaba

Actually, MySQL handles INT as INT(11) and INT UNSIGNED as INT(10).

In addition, Auto_Increment works for numeric fields, not just integers. See comment at the bottom of
http://www.mysql.com/doc/C/R/CREATE_TABLE.html for more details

Csaba

Anon

Since auto-incrementing columns are usually created with a NOT NULL constraint, this is not the best way to do this. The preferred method for most systems is to not include the column name, and let the dbms do it all.

create table ttt (n text, i autoincrement...)

insert into table ttt (n) values ('abc');

I.e. leave out the name of the autoincrement field altogether.

Postgresql will throw an error if you attempt to insert a null. Most other databases will fail as well.