PostgreSQL CHECK Constraint on columns other than foreign keys

Question

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

https://stackoverflow.com/questions/27107034/constraint-to-check-values-from-a-remotely-related-table-via-join-etc — Neoheurist, May 30 '17 at 18:47
https://dba.stackexchange.com/questions/91597/check-key-if-exists-in-other-table-without-fk-constraint — Neoheurist, May 30 '17 at 18:47

score 2 · Answer 1 · edited Jun 01 '17 at 21:52

2

You're solving problems that you've created yourself.

Your data model is a typical one-to-many relationship. You don't need an association table. Also, you don't need the same column in two related tables, one of them is redundant. Use the model as shown below to avoid typical problems resulting from 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
);

Using this approach there's no need to ensure that the reporting periods match between the associated records.

BTW, I would use text instead of varchar(n) and integer (serial) instead of uuid.

edited Jun 01 '17 at 21:52

Neoheurist

3,183
6
37
55

answered May 30 '17 at 19:47

klin

112,967
15
204
232

This approach vastly alters the intent of my data model - because upload files can legitimately not be associated to any reports - e.g. I still need to know the reporting period for the upload file independently of any reports to which it might (or might not) be associated. – Neoheurist Jun 01 '17 at 21:08
1

In a normalized model files associated with reports and those not associated are different datasets and should be stored in separate tables. As an alternative, you can add dummy entries (one for a year) to the reports to make both tables fully related. (Btw, the downvote is not mine). – klin Jun 01 '17 at 21:59
@Neoheurist: if a uploaded file is not associated with a report, then just store `null` in the `report_id` column. – Jul 27 '17 at 14:55

Neoheurist · Accepted Answer · 2017-07-27T14:50:25.943

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

Nothing is returned, so nothing is inserted in the table. https://www.postgresql.org/docs/current/static/plpgsql-trigger.html — JGH, May 31 '17 at 02:58

PostgreSQL CHECK Constraint on columns other than foreign keys

2 Answers2