Auto increment table column
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
Postgres 10 or later
(serial
columns remain unchanged, see below.)
Consider a standard-SQL IDENTITY
column. Can be GENERATED BY DEFAULT
or (stricter) GENERATED ALWAYS
.
Basics in the manual for CREATE TABLE
.
Details in this blog entry by its principal author Peter Eisentraut.
Create table with IDENTITY
column
CREATE TABLE staff (
staff_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, staff text NOT NULL
);
Add IDENTITY
column to existing table
Table may or may not be populated with rows.
ALTER TABLE staff ADD COLUMN staff_id int GENERATED ALWAYS 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 ALWAYS AS IDENTITY PRIMARY KEY;
See:
- How to add a PostgreSQL 10 identity column to an existing table with rows?
Replace serial
with IDENTITY
column
See:
- How to change a table ID from serial to identity?
You can override system values or user input in INSERT
commands with OVERRIDING {SYSTEM|USER} VALUE
.
Postgres 9.6 or older
(Still supported in newer versions, too.)
Use the serial
pseudo data type:
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 use legal, lower-case, unquoted identifiers in my examples. Makes your life with Postgres easier.