CONSTRAINT для проверки значений из удаленно связанной таблицы (через соединение и т. Д.)

Question

Я хотел бы добавить ограничение, которое будет проверять значения из связанной таблицы.

У меня есть 3 таблицы:

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

(Если вы хотите, чтобы я наложил ограничения на ФК, дайте мне знать.)

Я хочу добавить ограничение к somethink_usr_rel это проверяет type в role ("на расстоянии двух столов"), например:

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

Я пытался сделать это с JOINс, но не удалось. Есть идеи, как этого добиться?

Answer 1

CHECK В настоящее время ограничения не могут ссылаться на другие таблицы. По документации:

В настоящее время, CHECK выражения не могут содержать подзапросов или ссылаться на переменные, кроме столбцов текущей строки.

Одним из способов является использование триггера, подобного продемонстрированному @Wolph.

Чистое решение без триггеров (которое более надежно для обеспечения ссылочной целостности) состояло бы в добавлении избыточных столбцов и включении их в ограничения FK. Рассмотрите этот тесно связанный ответ на dba.SE с подробными инструкциями:

• Обеспечение соблюдения ограничений на расстоянии двух столов

Другим вариантом было бы "подделать" IMMUTABLE функцию, выполняющую проверку, и использовать ее в CHECK ограничение. Postgres позволит это, но помните о возможных последствиях. Вы лучше всего сделаете это NOT VALID ограничение. Подробности:

• Отключите все ограничения и проверки таблиц при восстановлении дампа

Answer 2

CHECK ограничение не вариант, если вам нужны объединения. Вы можете создать триггер, который вместо этого вызывает ошибку.

Посмотрите на этот пример: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html

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

... я так и сделал (nazwa= имя пользователя, firma = название компании):

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

Таблица может иметь более одного ограничения внешнего ключа. Это используется для реализации отношений «многие ко многим» между таблицами. Допустим, у вас есть таблицы о продуктах и ​​заказах, но теперь вы хотите, чтобы один заказ мог содержать множество продуктов (чего не позволяет структура выше). Вы можете использовать эту структуру таблицы:

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