On Thu, May 25, 2017 at 6:00 PM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote:
> The way I do it is the following : > - ensure a common sequence for the ID for all tables in the inheritance > tree (usually one parent and one or more children) > - enforce normal FK constraints for all FK relations within the same > "realm"/"tenant"/"schema" etc, i.e. where it makes sense > - for enforcing FK constraints between tables in different "realms", you > should implement this as a pair of CONSTRAINT triggers which implement the > two sides of the FK dependency. For the referencing tables you'd want to > check upon INSERT or UPDATE, with smth like : > > CREATE OR REPLACE FUNCTION > public.accounting_docs_cases_fk_to_public_accounting_docs() > RETURNS TRIGGER > LANGUAGE plpgsql > AS $$ > DECLARE > tmp INTEGER; > BEGIN > IF (TG_OP = 'DELETE') THEN > RAISE EXCEPTION 'TRIGGER : % called on unsuported op : > %',TG_NAME, TG_OP; > END IF; > SELECT ad.id INTO tmp FROM public.accounting_docs ad WHERE ad.id > =NEW.acct_doc_id; > IF NOT FOUND THEN > RAISE EXCEPTION '%''d % (id=%) with NEW.acct_doc_id (%) does not > match any accounting_docs ',TG_OP, TG_TABLE_NAME, NEW.id, NEW.acct_doc_id > USING ERRCODE = 'foreign_key_violation'; > END IF; > RETURN NEW; > END > $$ > ; > > -- here public.accounting_docs is a top level INHERITANCE table. Has > bcompanyFOO.accounting_docs and bcompanyBAR.accounting_docs as inherited > tables > > CREATE CONSTRAINT TRIGGER > accounting_docs_cases_fk_to_public_accounting_docs_tg > AFTER INSERT OR UPDATE > ON public.accounting_docs_cases FROM public.accounting_docs DEFERRABLE FOR > EACH ROW EXECUTE PROCEDURE public.accounting_docs_cases_f > k_to_public_accounting_docs(); > > For the referenced tables you'd want to check upon UPDATE or DELETE with > smth like : > > CREATE OR REPLACE FUNCTION > public.accounting_docs_fk_from_accounting_docs_cases() > RETURNS TRIGGER > LANGUAGE plpgsql > AS $$ > DECLARE > tmp INTEGER; > BEGIN > IF (TG_OP = 'INSERT') THEN > RAISE EXCEPTION 'TRIGGER : % called on unsuported op : > %',TG_NAME, TG_OP; > END IF; > IF (TG_OP = 'DELETE' OR OLD.id <> NEW.id) THEN > SELECT adc.id INTO tmp FROM accounting_docs_cases adc WHERE > adc.acct_doc_id=OLD.id; > IF FOUND THEN > RAISE EXCEPTION '%''d % (OLD id=%) matches existing > accounting_docs_cases with id=%',TG_OP, TG_TABLE_NAME, OLD.id,tmp USING > ERRCODE = 'foreign_key_violation'; > END IF; > END IF; > IF (TG_OP = 'UPDATE') THEN > RETURN NEW; > ELSE > RETURN OLD; > END IF; > END > $$ > ; > > CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases > AFTER DELETE OR UPDATE > ON public.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR EACH > ROW EXECUTE PROCEDURE accounting_docs_fk_from_accounting_docs_cases(); > > CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases > AFTER DELETE OR UPDATE > ON bcompanyFOO.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR > EACH ROW EXECUTE PROCEDURE accounting_docs_fk_from_accoun > ting_docs_cases(); > > CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases > AFTER DELETE OR UPDATE > ON bcompanyBAR.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR > EACH ROW EXECUTE PROCEDURE accounting_docs_fk_from_accoun > ting_docs_cases(); > > > Note that still this is not a proper case of a FK constraint, since this > requires a true common unique index across all tables of the inheritance > tree, which is not possible as of today. > > Thank you. This should work for me.
