Search code examples
auto-incrementsap-asealter

Alter column in table to auto increment in Sybase ASE 16.0

I am using Sybase ASE16.0 database in which I am trying to alter a column in an existing USER table so that it autoincrements every time a row is added to the table. The column: user_id is set as primary key and not null.

I have gone through many sybase tutorials and have tried many approaches but of no avail. Here are some queries that I wrote to make this change:

ALTER TABLE USER (user_id smallint IDENTITY not null)
ALTER TABLE USER ALTER user_id smallint IDENTITY not null
ALTER TABLE USER MODIFY user_id smallint NOT NULL IDENTITY
ALTER TABLE USER MODIFY user_id smallint NOT NULL AUTO_INCREMENT
ALTER TABLE USER MODIFY user_id smallint NOT NULL AUTOINCREMENT
ALTER TABLE USER ALTER user_id smallint NOT NULL AUTOINCREMENT
ALTER TABLE USER user_id smallint AUTOINCREMENT

I expect a SYBASE DB compliant query that would alter the user_id column in the table to autoincrement it by 1 on adding a new record

Solution

  • From documentation:

    Adds an IDENTITY column to a table. For each existing row in the table, Adaptive Server assigns a unique, sequential column value. The IDENTITY column could be type numeric or integer, and a scale of zero. The precision determines the maximum value (10 5 -1, or 99,999) that can be inserted into the column:

    alter table sales_daily add ord_num numeric (5,0) identity

    Found here