postgresql Doctrine2 没有将序列设置为 id 列的默认值（postgres）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6076180/
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
Doctrine2 doesn't set sequence to default for id column (postgres)
提问by Robertoq
Just a simple example: If I want create a table with auto fill id in postgres I run this sql:
只是一个简单的例子：如果我想在 postgres 中创建一个带有自动填充 ID 的表，我运行这个 sql：
CREATE SEQUENCE person_id_seq START 1;
CREATE TABLE person (
id integer PRIMARY KEY DEFAULT nextval('person_id_seq'),
name varchar(100) NOT NULL
);
and in doctrine I set all property
在教义上我设置了所有财产
class Person {
/**
* @Id
* @Column(type="integer", nullable=false)
* @GeneratedValue(strategy="SEQUENCE")
* @SequenceGenerator(sequenceName="person_id_seq", initialValue=1, allocationSize=100)
*/
private $id;
but when I generated sql (php doctrine orm:schema-tool:create --dump-sql) I got it:
但是当我生成 sql（php 学说 orm:schema-tool:create --dump-sql）时，我得到了它：
CREATE TABLE person (
id INT NOT NULL,
name VARCHAR(100) NOT NULL
);
CREATE SEQUENCE person_id_seq INCREMENT BY 100 MINVALUE 1 START 1
but don't set it to default
但不要将其设置为默认值
\d person
\d 人
Column | Type | Modifiers
-------------------+--------------------------------+-----------
id | integer | not null
...
..
.
回答by mu is too short
From the fine manual:
从精美的手册：
4.8.1. Identifier Generation Strategies
...AUTO(default): Tells Doctrine to pick the strategy that is preferred by the used database platform. The preferred strategies are IDENTITY for MySQL, SQLite and MsSQL and SEQUENCE for Oracle and PostgreSQL. This strategy provides full portability.
...IDENTITY: Tells Doctrine to use special identity columns in the database that generate a value on insertion of a row. This strategy does currently not provide full portability and is supported by the following platforms: MySQL/SQLite (AUTO_INCREMENT), MSSQL (IDENTITY) and PostgreSQL (SERIAL).
4.8.1. 标识符生成策略
...AUTO（默认）：告诉 Doctrine 选择所用数据库平台首选的策略。首选策略是 MySQL、SQLite 和 MsSQL 的 IDENTITY 以及 Oracle 和 PostgreSQL 的 SEQUENCE。这种策略提供了完全的可移植性。
...IDENTITY：告诉 Doctrine 在数据库中使用特殊的标识列，在插入行时生成一个值。此策略目前不提供完全的可移植性，并受以下平台支持：MySQL/SQLite (AUTO_INCREMENT)、MSSQL (IDENTITY) 和 PostgreSQL (SERIAL)。
They suggest AUTOfor maximum portability:
他们建议AUTO最大的便携性：
/**
* @Id
* @Column(type="integer", nullable=false)
* @GeneratedValue
*/
That should create and wire up a sequence for you. An alternative would be to ask for a serialcolumn using the IDENTITYstrategy:
这应该为您创建并连接一个序列。另一种方法是serial使用以下IDENTITY策略请求列：
/**
* @Id
* @Column(type="integer", nullable=false)
* @GeneratedValue(strategy="IDENTITY")
*/
This one should create your idcolumn as type serialand PostgreSQL will create the sequence and set up the default value for you.
这应该创建您的id列作为类型serial，PostgreSQL 将创建序列并为您设置默认值。
The documentation indicates that what you're doing should work but the documentation usually only provides a simplified version of reality.
文档表明您正在做的事情应该可行，但文档通常只提供简化版本的现实。
Try using strategy="AUTO". If that doesn't work, try strategy="IDENTITY".
尝试使用strategy="AUTO". 如果这不起作用，请尝试strategy="IDENTITY"。
回答by Tomasz Kuter
I encountered this problem today and I found that:
我今天遇到了这个问题，我发现：
IDENTITY work good, because it uses SERIAL type for PostgreSQL, which automatically creates related sequence and set default value as nextval(sequence)
AUTO creates table and then related sequence, but doesn't set default value for id column. It can be set by adding following code:
/** * Webpage's ID * * @ORM\Id * @ORM\Column(type="integer", options={"default"="nextval('webpages_id_seq'::regclass)"}) * @ORM\GeneratedValue(strategy="IDENTITY") */ protected $id;but unfortunately Doctrine create table first, so we need to swap SQL code creating table and sequence in order that sequence will be created first
SEQUENCE works the same as AUTO
IDENTITY 很好用，因为它在 PostgreSQL 中使用了 SERIAL 类型，它会自动创建相关序列并将默认值设置为 nextval(sequence)
AUTO 创建表，然后创建相关序列，但不为 id 列设置默认值。可以通过添加以下代码来设置：
/** * Webpage's ID * * @ORM\Id * @ORM\Column(type="integer", options={"default"="nextval('webpages_id_seq'::regclass)"}) * @ORM\GeneratedValue(strategy="IDENTITY") */ protected $id;但不幸的是，Doctrine 先创建表，所以我们需要交换创建表和序列的 SQL 代码，以便先创建序列
SEQUENCE 的工作原理与 AUTO 相同
回答by olidem
I also encounter, that the sequence is generated, but not assigned.
我也遇到过，序列是生成的，但没有分配。
So, this is the output of bin/console doctrine:schema:create --dump-sql
所以，这是输出 bin/console doctrine:schema:create --dump-sql
CREATE TABLE data_sample (id INT NOT NULL, hashvalue VARCHAR(64) DEFAULT NULL, date TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, refdate TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, value NUMERIC(20, 2) NOT NULL, PRIMARY KEY(id));
CREATE SEQUENCE data_sample_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
