Using Postgres, I'm trying to use AUTO_INCREMENT
to number my primary key automatically in SQL. However, it gives me an error.
CREATE TABLE Staff ( ID INTEGER NOT NULL AUTO_INCREMENT, Name VARCHAR(40) NOT NULL, PRIMARY KEY (ID) );
The error:
********** Error ********** ERROR: syntax error at or near "AUTO_INCREMENT" SQL state: 42601 Character: 63
Any idea why?
Syntax for Access Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5) . VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value.
Each table can have only one AUTO_INCREMENT column. It must defined as a key (not necessarily the PRIMARY KEY or UNIQUE key).
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
You can't have two auto-increment columns.
serial
columns (see below) remain unchanged. But consider an IDENTITY
column. Postgres 10 implements this standard-SQL feature.
Basic syntax and info in the manual for CREATE TABLE
.
Detailed explanation in this blog entry of its primary author Peter Eisentraut.
IDENTITY
columnCREATE TABLE staff ( staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY , staff text NOT NULL );
IDENTITY
column to existing tableTable may or may not be populated with rows.
ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY;
To also make it the PK at the same time (table can't have a PK yet):
ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;
Related:
serial
with IDENTITY
columnSee:
(Or any version, really.)
Use the serial
pseudo data type instead:
CREATE TABLE staff ( staff_id serial PRIMARY KEY, , staff text NOT NULL );
It creates and attaches the sequence object automatically and sets the DEFAULT
to nextval()
from the sequence. It does all you need.
I used lower case identifiers in my example. Makes your life with Postgres easier.
You do not specify which RDBMS you are using, however, in SQL Server you can use this syntax:
CREATE TABLE [dbo].[Staff] ( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] VARCHAR(40) NOT NULL, CONSTRAINT [ID] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With