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

Question

I would like to add a constraint that will check values from related table.

I have 3 tables:

CREATE TABLE somethink_usr_rel (
    user_id BIGINT NOT NULL,
    stomethink_id BIGINT NOT NULL
);

CREATE TABLE usr (
    id BIGINT NOT NULL,
    role_id BIGINT NOT NULL
);

CREATE TABLE role (
    id BIGINT NOT NULL,
    type BIGINT NOT NULL
);

(If you want me to put constraint with FK let me know.)

I want to add a constraint to somethink_usr_rel that checks type in role ("two tables away"), e.g.:

ALTER TABLE somethink_usr_rel
    ADD CONSTRAINT CH_sm_usr_type_check 
    CHECK (usr.role.type = 'SOME_ENUM');

I tried to do this with JOINs but didn't succeed. Any idea how to achieve it?

Answer 1

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

Answer 2

A CHECK constraint is not an option if you need joins. You can create a trigger which raises an error instead.

Have a look at this example: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when she must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Answer 3

...i did it so (nazwa=user name, firma = company name) :

CREATE TABLE users
(
  id bigserial  CONSTRAINT firstkey PRIMARY KEY,
  nazwa character varying(20),
  firma character varying(50)
);


CREATE TABLE test
(
  id bigserial  CONSTRAINT firstkey PRIMARY KEY,
  firma character varying(50),
  towar character varying(20),
  nazwisko character varying(20)
);

ALTER TABLE public.test ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION whoIAM3() RETURNS varchar(50) as $$
declare
    result varchar(50);
   BEGIN
 select into result users.firma from users where users.nazwa = current_user;
    return result;
    END;

    $$ LANGUAGE plpgsql;


CREATE POLICY user_policy ON public.test
    USING (firma = whoIAM3());

CREATE FUNCTION test_trigger_function()
RETURNS trigger AS $$
BEGIN
  NEW.firma:=whoIam3();
return NEW;
END
$$ LANGUAGE 'plpgsql'
CREATE TRIGGER test_trigger_insert BEFORE INSERT  ON test FOR EACH ROW EXECUTE PROCEDURE  test_trigger_function();

Answer 4

A table can have more than one foreign key constraint. This is used to implement many-to-many relationships between tables. Say you have tables about products and orders, but now you want to allow one order to contain possibly many products (which the structure above did not allow). You could use this table structure:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);