laravel 唯一违规：7 错误：重复键值违反唯一约束“users_pkey”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37970743/
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
Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_pkey"
提问by cyber8200
I'm using psql in my Laravel App. I'm trying to create my user, and I keep getting this error
我在我的 Laravel 应用程序中使用 psql。我正在尝试创建我的用户，但我不断收到此错误
Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_pkey"
唯一违规：7 错误：重复键值违反唯一约束“users_pkey”
Here what I did to store my user
这是我为存储用户所做的
$user = User::where('account_id','=',$id)->first();
$user->email = $account->email_address;
$user->fb_email = '';
$user->tw_email = '';
$user->fb_access_token = '';
$user->fb_profile_id = '';
$user->fb_page_id = '';
$user->fb_username = '';
$user->save();
Here is how I created my users table
这是我创建用户表的方式
CREATE TABLE "users" (
"id" serial NOT NULL ,
"account_id" varchar(255) NOT NULL ,
"email" varchar(255) NOT NULL ,
"fb_email" varchar(255) NOT NULL ,
"tw_email" varchar(255) NOT NULL ,
"created_at" timestamp(0) without time zone,
"updated_at" timestamp(0) without time zone,
"fb_access_token" varchar(255) NOT NULL ,
"fb_profile_id" varchar(255) NOT NULL ,
"fb_page_id" varchar(255) NOT NULL ,
"fb_username" varchar(255) NOT NULL ,
PRIMARY KEY ("id")
);
Did I do anything that I'm notsuppose to?
我做了我not认为应该做的任何事情吗？
What I am having right now used to work when I hook my app with MySQL.
当我将我的应用程序与MySQL.
Any hints / suggestions will mean a lot to me.
任何提示/建议对我来说都意义重大。
Screenshot
截屏
回答by patricus
Postgres handles auto incrementing a little differently than MySQL does. In Postgres, when you create the serialfield, you are also creating a sequence field that is keeping track of the id to use. This sequence field is going to start out with a value of 1.
Postgres 处理自动递增的方式与 MySQL 略有不同。在 Postgres 中，当您创建serial字段时，您同时也在创建一个序列字段来跟踪要使用的 id。此序列字段将从值 1 开始。
When you insert a new record into the table, if you don't specify the idfield, it will use the value of the sequence, and then increment the sequence. However, if you do specify the idfield, then the sequence is not used, and it is not updated, either.
当你在表中插入一条新记录时，如果你不指定id字段，它将使用序列的值，然后递增序列。但是，如果您确实指定了该id字段，则不会使用该序列，也不会更新该序列。
I'm assuming that when you moved over to Postgres, you seeded or imported some existing users, along with their existing ids. When you created these user records with their ids, the sequence was not used, and therefore it was never updated.
我假设当您转移到 Postgres 时，您植入或导入了一些现有用户以及他们现有的 ID。当您使用他们的 id 创建这些用户记录时，没有使用序列，因此它永远不会更新。
So, if, for example, you imported 10 users, you have users with ids 1-10, but your sequence is still at 1. When you attempt to create a new user without specifying the id, it pulls the value from the sequence (1), and you get a unique violation because you already have a user with id 1.
因此，例如，如果您导入了 10 个用户，那么您有 id 为 1-10 的用户，但您的序列仍为 1。当您尝试创建一个新用户而不指定 id 时，它会从序列中提取值 ( 1)，并且您得到一个唯一的违规，因为您已经有一个 ID 为 1 的用户。
To resolve the issue, you need to set your users_id_seqsequence value to the MAX(id) of your existing users. You can read this question/answerfor more information on resetting the sequence, but you can also try something like (untested):
要解决此问题，您需要将users_id_seq序列值设置为现有用户的 MAX(id)。您可以阅读此问题/答案以获取有关重置序列的更多信息，但您也可以尝试类似（未经测试）的方法：
SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id),1), false) FROM users;
FYI, this is not an issue in MySQL because MySQL automatically updates the auto increment sequence to the largest column value when a value is manually inserted into the auto incrementing field.
仅供参考，这在 MySQL 中不是问题，因为当手动将值插入自动递增字段时，MySQL 会自动将自动递增序列更新为最大的列值。
回答by hamed baftam
$tables = \DB::select('SELECT table_name FROM information_schema.tables WHERE table_schema = \'public\' ORDER BY table_name;');
$ignores = array('admin_setting', 'model_has_permissions', 'model_has_roles', 'password_resets', 'role_has_permissions', 'sessions');
foreach ($tables as $table) {
if (!in_array($table->table_name, $ignores)) {
$seq = \DB::table($table->table_name)->max('id') + 1;
\DB::select('ALTER SEQUENCE ' . $table->table_name . '_id_seq RESTART WITH ' . $seq);
}
}
