PostgreSQL CHECK对外键列以外的约束条件

问题描述：

我有一种情况，我想创建一个通过id关联其他表中的记录的表。关联的一个约束是，每年在关联的记录中年份必须相同......有没有办法让PostgreSQL在这个条件下的INSERT？PostgreSQL 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")

https://stackoverflow.com/questions/27107034/constraint-to-check-values-from-a-remotely-related-table-via-join-etc – Neoheurist

https://dba.stackexchange.com/questions/91597/check-key-if-exists-in-other-table-without-fk-constraint – Neoheurist

答

使用TRIGGER功能，我能够达到预期的效果：

CREATE FUNCTION "tenant".report_upload_files_create() RETURNS TRIGGER AS 
$report_upload_files_create$ 
    BEGIN 
    IF NOT EXISTS (
     SELECT 
     * 
     FROM 
     "tenant"."report", 
     "tenant"."upload_file" 
     WHERE 
     "tenant"."report"."id" = NEW."report_id" 
     AND 
     "tenant"."upload_file"."id" = NEW."upload_file_id" 
     AND 
     "tenant"."report"."reporting_period" = "tenant"."upload_file"."reporting_period" 
    ) 
    THEN 
     RAISE EXCEPTION 'Report and Upload File reporting periods do not match'; 
    END IF; 

    RETURN NEW; 
    END 

$report_upload_files_create$ LANGUAGE plpgsql; 

CREATE TRIGGER "report_upload_files_create" BEFORE INSERT ON "tenant"."report_upload_files" 
    FOR EACH ROW EXECUTE PROCEDURE "tenant".report_upload_files_create();

什么也没有返回，所以没有插入表中。 https://www.postgresql.org/docs/current/static/plpgsql-trigger.html – JGH

随着返回语句的添加，它现在是一个有效的答案 – JGH

答

你解决了，你自己创建的问题。

您的数据模型是典型的一对多关系。你不需要关联表。另外，您不需要两个相关表中的相同列，其中一个是多余的。使用如下所示的模型以避免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。

这种方法极大地改变了我的数据模型的意图 - 因为上传文件可以合法地不与任何报告相关 - 例如我仍然需要知道上传文件的报告期限，而不管它可能（也可能不会）关联的任何报告。 – Neoheurist

在规范化模型中，与报告关联的文件和不关联的文件是不同的数据集，应存储在单独的表中。作为替代方案，您可以向报告中添加虚拟条目（一年为一年），以使两个表格完全相关。（顺便说一句，downvote不是我的）。 – klin

@Neoheurist：如果上传的文件没有与报告相关联，那么只需在'report_id'列中存储'null'。 –

PostgreSQL CHECK对外键列以外的约束条件

相关推荐