Automatically increment Primary Key ussing Standard SQL

[DCrupier]

Hi!
How can I automatically increment my Primary Keys ussing Standard SQL?
I mean without ussing objects like Oracle Sequences or SQL Server Identities.
I repeat: ussing Standard SQL.

Thnx

 

[pain4u]

In the table definition.

CREATE TABLE table_name(column_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT); - PAINKILLER

 

[karluk]

I can't think of any better way to do this in standard ANSI SQL than to store the next value of the primary key in a second table. The second table would need only a single row. You would query it when inserting a row to get the next key, and then add 1 to keep the value up to date.

Obviously this is logically equivalent to an Oracle sequence, but doesn't require you to be using Oracle. If portability is a big concern, it may be worth the extra overhead.