Auto Incrementing an SQL column which doesnt have the AUTO INCREMENT constraint

You need to use a sequence. You can see the available sequences in a schema in Squirrel SQL. These sequences can be auto generated or manually created. A sequence is generated automatically when you make a column on any table auto increment. This is the case for the 'SYSTEM_SEQUENCE_XX..' below. The other sequences were manually written.


sql-sequence-screenshot

The list of sequences seen below the tables

The syntax for inserting into a table which has an id whcih cant be null and needs to increment, but which doesnt have an auto increment constraint, is below:

INSERT INTO AUTHORITY(order_id, foo1, foo2, foo3)SELECT AUTHORITY_PK_SEQ.nextval,foo1, foo2, foo3
                                FROM bar
                                WHERE X = Y
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: