Skip to main content
A
Anonymous
September 29, 2010
Answered

AutoNumber Issues

  • September 29, 2010
  • 1 reply
  • 837 views

I am trying to write an SQL statement to create a column that I want to auto increment with each row entry in to the database. I execute the SQL statement CREATE TABLE states (stateID int NOT NULL AUTO_INCREMENT,

                                                      stateName varchar (50),

                                                       stateAbbr varchar (2))

I get the error message, "Syntax error: Encountered "AutoIncrement" at line 3, column 22. How do I create a variable that auto increments?

This topic has been closed for replies.
Correct answer tooMuchTrouble

try this:

CREATE TABLE whatever (

StateID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT

BY 1),

blah,

blah,

blah,

and see this:

http://db.apache.org/derby/docs/10.1/ref/rrefsqlj37836.html

1 reply

T
tooMuchTrouble
Inspiring
September 29, 2010

depends on the db.

A
Anonymous
September 30, 2010

The database uses Apache Derby Embedded drivers. It is a modification of the OWS database using in the ColdFusion 8 textbook by Ben Forta. I am attempting to add a table to this database. In the table, I want to add a column (stateID) that will automatically increment by 1 each time a new state and state abbreviation is added.

T
tooMuchTroubleCorrect answer
Inspiring
September 30, 2010

try this:

CREATE TABLE whatever (

StateID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT

BY 1),

blah,

blah,

blah,

and see this:

http://db.apache.org/derby/docs/10.1/ref/rrefsqlj37836.html

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices