CONSTRAINT per verificare i valori da una tabella correlata a distanza (tramite join ecc.)

Vorrei aggiungere un vincolo che controllerà i valori dalla tabella correlata.

Ho 3 tavoli:

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

(Se vuoi che imponga un vincolo con FK fammelo sapere.)

Voglio aggiungere un vincolo a somethink_usr_rel che controlla il type di role (“due tavoli di distanza”), ad esempio:

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

Ho provato a farlo con JOIN s ma non ci sono riuscito. Qualche idea su come ottenerlo?

CHECK vincoli CHECK non possono attualmente fare riferimento ad altre tabelle. Per documentazione:

Attualmente, le espressioni CHECK non possono contenere subquery né fare riferimento a variabili diverse dalle colonne della riga corrente.

Un modo è utilizzare un trigger come dimostrato da @Wolph .

Una soluzione pulita senza trigger (che è più robusta per rafforzare l’integrità referenziale) sarebbe quella di aggiungere colonne ridondanti e includerle nei vincoli FK. Considera questa risposta strettamente correlata su dba.SE con istruzioni dettagliate:

  • Esecuzione dei vincoli “a due tavoli di distanza”

Un’altra opzione sarebbe quella di “falsificare” una funzione IMMUTABLE eseguendo il controllo e utilizzandola in un vincolo CHECK . Postgres lo permetterà, ma sii consapevole delle possibili conseguenze. È meglio renderlo un vincolo NOT VALID . Dettagli:

  • Disabilitare tutti i vincoli e i controlli delle tabelle durante il ripristino di un dump

Un vincolo CHECK non è un’opzione se hai bisogno di join. Puoi creare un trigger che genera invece un errore.

Dai un’occhiata a questo esempio: 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(); 

… l’ho fatto così (nazwa = nome utente, firma = nome azienda):

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