Re: Inheritance and foreign keys

On Thu, May 25, 2017 at 6:00 PM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)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.