Laravel 5.3口才交易和外键限制 [英] Laravel 5.3 Eloquent transactions and foreign key restrictions

问题描述

我正在做一个更大的项目，在这个项目中我们在一个Postgres DB中有多个模式。我们在模式之间创建了外键。这是一个示例>
我们有公司架构和用户架构。公司架构具有company_users表，该表对user.users表具有外键限制

Am working on bigger project where we have multiple schemas in one Postgres DB. We have created foreign keys between schemas. Here is an example > We have company schema and user schema. Company schema has company_users table which have foreign key restriction on user.users table

CREATE TABLE company.company_user
(
  id serial NOT NULL,
  company_id integer NOT NULL,
  user_id integer NOT NULL,
  created_at timestamp(0) without time zone,
  updated_at timestamp(0) without time zone,
  deleted_at timestamp(0) without time zone,
  CONSTRAINT company_user_pkey PRIMARY KEY (id),
  CONSTRAINT company_user_company_id_foreign FOREIGN KEY (company_id)
      REFERENCES company.companies (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT company_user_user_id_foreign FOREIGN KEY (user_id)
      REFERENCES "user".users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

以下查询在Postgres中运行没有问题

Following queries run in Postgres without issue

BEGIN;
insert into "db"."user"."users" (id,"surname", "firstname", "email", "position", "language_id", "is_super_admin", "updated_at", "created_at") values (156,'Mueller', 'Julianne', '[e-mail]', 'Nuclear Power Reactor Operator', 41, false, '2017-01-13 12:35:10', '2017-01-13 12:35:10') returning "id";
insert into "db"."company"."company_user" ("company_id", "user_id", "updated_at", "created_at") values (4445, 156, '2017-01-13 12:35:10', '2017-01-13 12:35:10') returning "id";
COMMIT;

但是如果我通过Laravel在Eloquent中执行相同的查询

However if i perform same queries via Eloquent in Laravel

\DB::beginTransaction();
$user = new User(["surname" => 'Mueller',
                  "firstname" => 'Julianne',
                  "email" => '[e-mail]',
                  "position" => 'Nuclear Power Reactor Operator',
                  "language_id" => 41,
                  "is_super_admin" => false]
);
if (!$user->save()) {
    \DB::rollBack();
    return false;
}
\Log::error($user->id);
$company_user = new CompanyUser([
    "company_id" => 4445,
    "user_id" => $user->id
]);
if (!$company_user->save()) {
    \DB::rollBack();
    return false;
}
\DB::commit();

抛出以下错误（似乎无法在表中找到用户ID）

is throwing folloing error (it seems that it cannot find id of user in the table)

PDOException: SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "company_user" violates foreign key constraint "company_user_user_id_foreign"

有人可以说为什么这不起作用吗？ \Log :: error（$ user-> id）正在打印插入用户的ID。我试图使用数据库侦听器从Laravel打印出查询，所有查询均以正确的顺序执行，但仍然出现此错误。

Would anyone can say why this is not working? \Log::error($user->id) is printing id of inserted user. I tried to print out queries from Laravel with DB listener, all queries are executed in correct order, but am still getting this error.

推荐答案

好的，所以我们找到了一个解决方案。似乎我们需要分别为每个架构开始事务，并且每个递归引用与自己架构不同的架构的外键都应按延迟创建。

Ok so we found a solution. It seems that we need to start transaction for each of schemas separately + each foreign key that are referencing different schema than their own should be created as deferred.

这篇关于Laravel 5.3口才交易和外键限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！