Auto number as table id

[Anon]

Hi, I wish to set up a table which has a primary key of ID. This ID needs to be an auto generated number that increments by one every time a new row of data is entered into the table. I have tried setting the column to be auto_generated.

This is fine except when records in the table have been deleted and a new record is inserted the ID becomes the last available highest ID + 1 (therefore it is not unique). Is there anyway like in SQL_Server for instance you can set a column to be unique and auto incremented??

Any help much appreciated.

[Anon]

The syntax for autoincrementing columns is unique to most databases.

for postgresql its:

create table test(name text, id serial);

the serial keyword is an alias for the autoincrement, int4, notnull stuff that you put in in most other dbs.

Someone else will have to fill in the blanks if you're running mysql, but if you search this forum, you'll find the answer.