postgresql - Laravel 5.3 Eloquent 事务和外键限制

Question

我正在从事更大的项目，我们在一个 Postgres 数据库中有多个模式。我们在模式之间创建了外键。这是一个例子 >我们有公司模式和用户模式。公司模式有 company_users 表，它对 user.users 表有外键限制

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 中运行没有问题

BEGIN;
insert into "db"."user"."users" (id,"surname", "firstname", "email", "position", "language_id", "is_super_admin", "updated_at", "created_at") values (156,'Mueller', 'Julianne', 'julianne.mueller1@example.org', '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 执行相同的查询

\DB::beginTransaction();
$user = new User(["surname" => 'Mueller',
                  "firstname" => 'Julianne',
                  "email" => 'julianne.mueller1@example.org',
                  "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)

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。我尝试使用 DB 监听器从 Laravel 打印出查询，所有查询都以正确的顺序执行，但仍然出现此错误。

Answer 1

好的，我们找到了解决方案。看来我们需要分别为每个模式启动事务 + 每个引用不同模式的外键应该被创建为延迟。