SQL 自增表列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9875223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Auto increment table column
提问by Jimmy
Using Postgres, I'm trying to use AUTO_INCREMENTto number my primary key automatically in SQL. However, it gives me an error.
使用 Postgres，我试图AUTO_INCREMENT在 SQL 中自动给我的主键编号。但是，它给了我一个错误。
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
********** Error ********** ERROR: syntax error at or near "AUTO_INCREMENT" SQL state: 42601 Character: 63
Any idea why?
知道为什么吗？
回答by Erwin Brandstetter
Postgres 10 or later
Postgres 10 或更高版本
serialcolumns (see below) remain unchanged. But consider an IDENTITYcolumn. Postgres 10 implements this standard-SQL feature.
serial列（见下文）保持不变。但是考虑一个IDENTITY列。Postgres 10 实现了这个标准的 SQL 特性。
CREATE TABLE staff (
staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, staff text NOT NULL
);Basic syntax and info in the manual for CREATE TABLE.
Detailed explanation in this blog entry of its primary author Peter Eisentraut.
手册CREATE TABLE中的基本语法和信息。其主要作者 Peter Eisentraut 的
这篇博客条目中的详细解释。
To addan IDENTITYcolumn to a pre-existing table (populated with rows or not):
要添加的IDENTITY列到预先存在的表（填充有行或不）：
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):
同时使其成为PK（表还不能有PK）：
ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;
There was a bug in early versions, that could lead to an error message like:
早期版本中存在一个错误，可能会导致如下错误消息：
ERROR: column "staff_id" contains null values
ERROR: column "staff_id" contains null values
This was fixed with Postgres 10.2. Details:
Postgres 10.2 修复了这个问题。细节：
Postgres 9.6 or older
Postgres 9.6 或更高版本
Use the serialpseudo data typeinstead:
改用serial伪数据类型：
CREATE TABLE staff (
staff_id serial PRIMARY KEY,
, staff text NOT NULL
);It creates and attaches the sequence object automatically and sets the DEFAULTto nextval()from the sequence. It does all you need.
它会自动创建并附加序列对象，并从序列中设置DEFAULTto nextval()。它可以满足您的所有需求。
I also use just lower case identifiersin my example. Makes your life with Postgres easier.
在我的示例中，我也只使用小写标识符。使用 Postgres 让您的生活更轻松。
And better use descriptive column names. "id" as name is an anti-pattern, used by some middle-ware, but hardly descriptive. Similar with "name".
最好使用描述性的列名。“id”作为名称是一种反模式，被一些中间件使用，但几乎没有描述性。与“姓名”相似。
回答by Royi Namir
In the SQL server database you can use Identity(1,1)like this:
在 SQL Server 数据库中，您可以Identity(1,1)像这样使用：
CREATE TABLE Staff
(
ID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(40) NOT NULL,
PRIMARY KEY (ID)
);
回答by Barry Kaye
You do not specify which RDBMS you are using, however, in SQL Server you can use this syntax:
您没有指定您使用的是哪个 RDBMS，但是，在 SQL Server 中您可以使用以下语法：
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
回答by Eric Leschinski
PostgreSQL: If you absolutely must have your own auto increment value:
PostgreSQL：如果您绝对必须拥有自己的自动增量值：
Then use a sequence:
然后使用一个序列：
ericlesc_schools=> drop table yar;
DROP TABLE
ericlesc_schools=> drop sequence user_id_seq;
DROP SEQUENCE
ericlesc_schools=> create sequence user_id_seq;
CREATE SEQUENCE
ericlesc_schools=> create table yar(
id int default nextval('user_id_seq'),
foobar varchar);
CREATE TABLE
ericlesc_schools=> insert into yar (foobar) values('hey alex');
INSERT 0 1
ericlesc_schools=> insert into yar (foobar) values('hey what derick');
INSERT 0 1
ericlesc_schools=> insert into yar (foobar) values('I look like a hushpuppy');
INSERT 0 1
ericlesc_schools=> select * from yar;
id | foobar
----+-----------------
1 | hey alex
2 | hey what derick
3 | I look like a hushpuppy
(3 rows)
