唯一违规:7错误:重复的键值违反了唯一约束"users_pkey" [英] Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_pkey"

问题描述

我在Laravel App中使用psql. 我正在尝试创建我的用户，并且不断收到此错误

I'm using psql in my Laravel App. I'm trying to create my user, and I keep getting this error

唯一违反:7错误:重复的键值违反唯一约束"users_pkey"

Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_pkey"

---

这是我存储用户的操作

$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();

---

这是我创建用户表的方式

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")

);

我做了not想做的事情吗?

Did I do anything that I'm not suppose to?

当我用MySQL钩住我的应用程序时，我现在拥有的东西正常工作.

What I am having right now used to work when I hook my app with MySQL.

任何提示/建议对我来说都意义非凡.

Any hints / suggestions will mean a lot to me.

推荐答案

Postgres处理自动增量的方式与MySQL有所不同.在Postgres中，创建serial字段时，还将创建一个序列字段，该字段跟踪要使用的id.该序列字段将从值1开始.

Postgres handles auto incrementing a little differently than MySQL does. In Postgres, when you create the serial field, 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.

在表中插入新记录时，如果不指定id字段，它将使用序列的值，然后递增序列.但是，如果您确实指定了id字段，则不会使用该序列，也不会对其进行更新.

When you insert a new record into the table, if you don't specify the id field, it will use the value of the sequence, and then increment the sequence. However, if you do specify the id field, then the sequence is not used, and it is not updated, either.

我假设当您移至Postgres时，您为一些现有用户及其现有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.

因此，例如，如果您导入了10个用户，则您具有ID为1-10的用户，但是您的序列仍为1.当您尝试创建一个没有指定ID的新用户时，它将值从序列(1)，由于您已经有一个ID为1的用户，因此您会得到一个唯一的违规.

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.

要解决此问题，您需要将users_id_seq序列值设置为现有用户的MAX(id).您可以阅读此问题/答案以获取有关重置序列的更多信息，但是您也可以尝试类似(未经测试)的操作:

To resolve the issue, you need to set your users_id_seq sequence value to the MAX(id) of your existing users. You can read this question/answer for more information on resetting the sequence, but you can also try something like (untested):

SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id),1), false) FROM users;

---

仅供参考，这在MySQL中不是问题，因为当将值手动插入到自动递增字段中时，MySQL会自动将自动递增序列更新为最大列值.

---

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.

这篇关于唯一违规:7错误:重复的键值违反了唯一约束"users_pkey"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！