sql - PostgreSQL CHECK 约束外键以外的列

Question

我有一种情况，我想创建一个表，通过 id 关联来自其他表的记录。关联的约束是每个表中关联的记录中的年份必须相同...有没有办法让 PostgreSQL 在 INSERT 上CHECK 这个条件>?

表 1:

CREATE TABLE "tenant"."report" (
  "id" UUID NOT NULL DEFAULT "pascal".uuid_generate_v1(),
  CONSTRAINT "report_pkc_id" PRIMARY KEY ("id"),

  "reporting_period" integer NOT NULL,
  "name" VARCHAR(64) NOT NULL,
  CONSTRAINT "report_uc__name" UNIQUE ("reporting_period", "name"),

  "description" VARCHAR(2048) NOT NULL
);

表 2:

CREATE TABLE "tenant"."upload_file" (
  "id" UUID NOT NULL DEFAULT "pascal".uuid_generate_v1(),
  CONSTRAINT "upload_file_pkc_id" PRIMARY KEY ("id"),

  "file_name" VARCHAR(256) NOT NULL,

  "reporting_period" integer
)

关联表:

CREATE TABLE "tenant"."report_upload_files"
(
  "report_id" UUID NOT NULL,
  CONSTRAINT "report_upload_files_pkc_tenant_id" PRIMARY KEY ("report_id"),
  CONSTRAINT "report_upload_files_fkc_tenant_id" FOREIGN KEY ("report_id")
  REFERENCES "tenant"."report" ("id") MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE,

  "upload_file_id" UUID NOT NULL,
  CONSTRAINT "report_upload_files_fkc_layout_id" FOREIGN KEY ("upload_file_id")
  REFERENCES "tenant"."upload_file" ("id") MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE
)

我想在关联表 CREATE 语句中添加类似的内容:

CHECK ("tenant"."report"."reporting_period" = "tenant"."upload_file"."reporting_period")

Answer 1

您正在解决您自己造成的问题。

您的数据模型是典型的一对多关系。您不需要关联表。此外，您不需要两个相关表中的同一列，其中一个是多余的。使用如下所示的模型以避免 lack of normalization 导致的典型问题.

create table tenant.report (
    id uuid primary key default pascal.uuid_generate_v1(),
    reporting_period integer not null,
    name varchar(64) not null,
    description varchar(2048) not null,
    unique (reporting_period, name)
);

create table tenant.upload_file (
    id uuid primary key default pascal.uuid_generate_v1(),
    report_id uuid references tenant.report(id),
    file_name varchar(256) not null
);

使用此方法无需确保报告期间在相关记录之间匹配。

顺便说一句，我会使用 text 而不是 varchar(n) 和 integer (serial) 而不是 uuid.