Add conditional constraint check

Question

I'm using PostgreSQL 9.2 and need to add a conditional constraint on a column. Essentially, I want to make sure that a column is false when two other columns have a certain value.

Table definition:

gid          | int_unsigned           | not null default 0
realm        | character varying(255) | not null default ''::character varying
grant_update | smallint_unsigned      | not null default (0)::smallint
grant_delete | smallint_unsigned      | not null default (0)::smallint

Example:

alter table node_access add constraint block_anonymous_page_edit
check (grant_update = 0 WHERE (gid = 1 AND realm = 'nodeaccess_rid'));

This is supposed to make sure that grant_update is equal to 0 when gid is 1 and realm = nodeaccess_rid.

However, I think rather than doing what I want, it's actually trying to make all columns mimic these values. In essence, it's trying to make sure that grant_update is always 0, gid is always 1, and realm is always nodeaccess_rid. The error I get is:

ERROR: check constraint "block_anonymous_page_edit" is violated by some row

Maybe a function that gets triggered on update?

`int_unsigned` and `smallint_unsigned` are non-existing types in Postgres. Please clean up your question. — Erwin Brandstetter, Feb 10 '13 at 07:13
Non-existent or not, that's how our database rolls. The question, as it stands, is correct. — PREEB, Feb 11 '13 at 17:28

Erwin Brandstetter · Accepted Answer · 2021-10-12T13:46:46.943

35

Once you wrap your mind around the logic, it's simple CHECK constraint:

CREATE TABLE tbl (
  gid          int      NOT NULL DEFAULT 0
, realm        text     NOT NULL DEFAULT ''
, grant_update smallint NOT NULL DEFAULT 0
, CONSTRAINT block_anonymous_page_edit
  CHECK (gid <> 1 OR realm <> 'nodeaccess_rid' OR grant_update = 0)
);

Test:

-- these work:
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 0);

INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'some_string',    1);

INSERT INTO tbl(gid, realm, grant_update)
VALUES (2, 'nodeaccess_rid', 1);

-- check violation!
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 1);

db<>fiddle here

edited Oct 12 '21 at 13:46

answered Feb 10 '13 at 07:37

Erwin Brandstetter

605,456
145
1,078
1,228

I altered your solution slightly to incorporate an aspect I didn't include in my question. There's an additional attribute that allows users or anonymous to delete pages, not just update them. So now it looks like this, `CHECK (gid <> 1 OR realm <> 'nodeaccess_rid' OR grant_update = 0 OR grant_delete = 0)` – PREEB Feb 11 '13 at 18:24
1

The actual solution looked like this, `alter table if exists node_access add constraint chk_block_anonymous_page_edit check (gid <> 1 or realm <> 'nodeaccess_rid' or grant_update = 0 or grant_delete = 0);` – PREEB Feb 11 '13 at 18:30
I assume you are aware that this constraint allows `grant_update` *or* `grant_delete` to be `0` for said case, but not necessarily both. – Erwin Brandstetter Feb 12 '13 at 08:55
Correct. Works as desired. Thanks for the input. – PREEB Feb 12 '13 at 20:21
1

By the way i suggest to give the CHECK constraint a NAME using the alternative syntax CONSTRAINT my_check CHECK (gid <> 1 OR realm <> 'nodeaccess_rid' OR grant_update = 0) ... it would be very useful when you need to disable, drop, or alter the constraint by any reason. – Rafael Jul 03 '14 at 07:36
To clarify: *every* check constraint has a name. If you don't provide one, the system picks a default. Get list of constraints for your table with `SELECT * FROM pg_constraint WHERE conrelid = 'tbl'::regclass`. – Erwin Brandstetter Jul 03 '14 at 16:19

score 3 · Answer 2 · answered Feb 08 '13 at 16:03

3

I would write this as a trigger. This gives you the flexibility of either raising an error (potentially with a custom code that can best tested for) or just handling the problem and setting grant_update = 0 when gid=1 and realm = 'nodeaccess_rid'

answered Feb 08 '13 at 16:03

David S

12,967
12
55
93

score -2 · Answer 3 · answered Feb 08 '13 at 21:10

I ended up going with the trigger function. This will check the role and set the unwanted functionality to off with the boolean-ish fields grant_update and grant_delete. The function below also preserves the grant_view value rather than overwriting it.

CREATE OR REPLACE function block_anonymous_page_edit()
RETURNS trigger AS $function$
BEGIN
  IF NEW.gid = 1 AND NEW.realm != 'nodeaccess_author' AND (NEW.grant_update = 1 OR NEW.grant_delete = 1) THEN
    RAISE WARNING 'Anonymous users are not allowed to edit pages.';
    NEW.grant_update := 0;
    NEW.grant_delete := 0;
  END IF;
  RETURN NEW;
END;
$function$ LANGUAGE plpgsql;

CREATE TRIGGER tgr_block_anonymous_page_edit BEFORE INSERT OR UPDATE ON node_access FOR EACH ROW EXECUTE PROCEDURE block_anonymous_page_edit();

Add conditional constraint check

3 Answers3