foreign - postgresql reference serial

CONSTRAINT para verificar los valores de una tabla relacionada de forma remota(mediante unión, etc.)

Question

Me gustaría agregar una restricción que verifique los valores de la tabla relacionada.

Tengo 3 mesas:

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

(Si quieres que ponga restricciones con FK, házmelo saber).

Quiero agregar una restricción a somethink_usr_rel que verifique el type en el role ("a dos tablas de distancia"), por ejemplo:

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

Traté de hacer esto con JOIN s pero no tuve éxito. ¿Alguna idea de cómo lograrlo?

Answer 1

... lo hice así (nazwa = nombre de usuario, firma = nombre de la empresa):

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 2

Una restricción CHECK no es una opción si necesita uniones. Puede crear un disparador que genera un error en su lugar.

Eche un vistazo a este ejemplo: 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

CHECK restricciones CHECK no pueden hacer referencia actualmente a otras tablas. El manual:

Actualmente, las expresiones CHECK no pueden contener subconsultas ni hacer referencia a variables distintas de las columnas de la fila actual.

Una forma es usar un disparador como lo demostró @Wolph .

Una solución limpia sin disparadores : agregue columnas redundantes e inclúyalas en las restricciones de FOREIGN KEY, que son la primera opción para imponer la integridad referencial de todos modos. Respuesta relacionada en dba.SE con instrucciones detalladas:

• Cumplimiento de restricciones "a dos tablas de distancia"

Otra opción sería "falsificar" una función INMUTABLE haciendo la comprobación y usarla en una restricción CHECK . Postgres lo permitirá, pero tenga en cuenta las posibles consecuencias. Lo mejor es hacer que una restricción NOT VALID . Detalles:

• Deshabilite todas las restricciones y verificaciones de tablas mientras restaura un volcado