Postgresql Check Constraint for Foreign Key Condition

PostgreSQL check constraint for foreign key condition

This would work for INSERTS:

create or replace function is_superuser(int) returns boolean as $$
select exists (
    select 1
    from "user"
    where id   = $1
      and superuser = true
);
$$ language sql;

And then a check contraint on the user_has_job table:

create table user_has_job (
    user_id integer references "user"(id),
    job_id integer references job(id),
    constraint user_has_job_pk PRIMARY KEY (user_id, job_id),
    constraint chk_is_superuser check (is_superuser(user_id))
);

Works for inserts:

postgres=# insert into "user" (name,superuser) values ('name1',false);
INSERT 0 1
postgres=# insert into "user" (name,superuser) values ('name2',true);
INSERT 0 1

postgres=# insert into job (description) values ('test');
INSERT 0 1
postgres=# insert into user_has_job (user_id,job_id) values (1,1);
ERROR:  new row for relation "user_has_job" violates check constraint "chk_is_superuser"
DETAIL:  Failing row contains (1, 1).
postgres=# insert into user_has_job (user_id,job_id) values (2,1);
INSERT 0 1

However this is possible:

postgres=# update "user" set superuser=false;
UPDATE 2

So if you allow updating users you need to create an update trigger on the users table to prevent that if the user has jobs.

Conditional PostgreSQL foreign key

You can add another "shadow" column to table1 which holds the cleaned values (i.e. everything but 0 and -1). Use this column for the referential integrity checks. This shadow column is updated/filled by a simple trigger on table1 which writes all values but 0 and -1 into the shadow column. Both 0 and -1 could be mapped to null.

Then you have reference integrity and your unchanged original column. The downside: You have also a little trigger and some redundant data. But alas, this is the fate of a legacy schema!

SQL check constraint with multiple conditions

You can write the constraints as:

ALTER TABLE timesheets
  ADD constraint just_user__or__location_or_customer_with_user__or__just_task check (
      (
        user_id is not null
        and task_schedule_id is null
        and (
          (location_id is null and customer_id is null)
          and (location_id is not null or customer_id is not null)
        )
      ) or (
        (location_id is not null or customer_id is not null)
        and not (location_id is not null and customer_id is not null)
        and user_id is not null
      ) or (
        task_schedule_id is not null
        and user_id is null
        and location_id is null
        and customer_id is null
      )
    );

PostgreSQL Foreign Key with a condition

I'd change foreign_key_on_table_a_id to allow NULL values. Then use an FK as usual and put NULLs in there instead of zero. You can have a NULL in a column that references another table.

Alternatively, you could write a function that returns true if a value is in the other table and false otherwise and then add a CHECK constraint:

CHECK (your_column = 0 or the_function(your_column))

You won't get any of the usual cascade behavior for FKs though and this CHECK is a massive kludge.

CONSTRAINT to check values from a remotely related table (via join etc.)

CHECK constraints cannot currently reference other tables. The manual:

Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row.

One way is to use a trigger like demonstrated by @Wolph.

A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the first choice to enforce referential integrity. Related answer on dba.SE with detailed instructions:

• Enforcing constraints “two tables away”

Another option would be to "fake" an IMMUTABLE function doing the check and use that in a CHECK constraint. Postgres will allow this, but be aware of possible caveats. Best make that a NOT VALID constraint. See:

• Disable all constraints and table checks while restoring a dump

How to make a foreign key with a constraint on the referenced table in PostgreSQL

You can use a CHECK constraint for this. You can't put a query in a CHECK constraint but you can call a function; so, we build a simple function that tells us if a pluginid is a matrix:

create or replace function is_matrix(int) returns boolean as $$
    select exists (
        select 1
        from plugins
        where id   = $1
          and type = 'matrix'
    );
$$ language sql;

and wrap that in a CHECK constraint:

alter table matrix_params add constraint chk_is_matrix check (is_matrix(pluginid));

Then:

=> insert into matrix_params values (1,1);
=> insert into matrix_params values (2,3);
ERROR:  new row for relation "matrix_params" violates check constraint "chk_is_matrix"

And the FK takes care of referential integrity and cascades.

PostgreSQL CHECK Constraint on columns other than foreign keys

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

Postgres constraint and foreign key

Unless I misunderstand you, PostgreSQL already works the way you want by default:

• You can have the same entries twice in a UNIQUE constraint as long as one of them is NULL.

• If a foreign key column is NULL, the constraint is not enforced, as long as you stick with the default MATCH SIMPLE.

• For a condition like “one of two values must be NOT NULL”, you can use a check constraint.

PostgreSQL - Constraint Based on Column in Another Table

I would suggest that you modify your data model to have a table, PollOptions:

CREATE TABLE IF NOT EXISTS PollOptions (
  PollOptionsId SERIAL PRIMARY KEY,  -- should use generated always as identity
  PollId INT NOT NULL, REFERENCES Polls(id),
  OptionNumber int,
  Option text,
  UNIQUE (PollId, Option)
);

Then your Votes table should have a foreign key reference to PollOptions. You can use either PollOptionId or (PollId, Option).

No triggers or special functions are needed if you set up the data correctly.

---

Related Topics