Neoheurist Neoheurist - 2 years ago 133
SQL Question

PostgreSQL CHECK Constraint on columns other than foreign keys

I have a situation where I want to create a table that associates records from other tables by the id. A constraint of the association is that the year must be the same in the record being associated in each table... Is there a way to get PostgreSQL to

CHECK
this condition on
INSERT
?

Table 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
);


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


Association Table:

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
)


I want to add something like to the association table
CREATE
statement:

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

Answer Source

Using a TRIGGER function I was able to achieve the desired effect:

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();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download