Inheritance and foreign keys

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Inheritance and foreign keys

Jayadevan
Hi,

I designed three tables so that one table inherits another, and the third table references the parent table. If a record is inserted into the third table and the value does exist in the parent table indirectly, because it is present in the inherited table, I still get an error. 
Is some option available while creating the foreign key so that it will consider the data in the child tables also while doing a constraint validation?

create table myt(id serial primary key);
create table mytc (like myt);
alter table mytc inherit myt;
insert into myt values(1);
insert into mytc values(2);
 select * from myt;
 id 
----
  1
  2

create table a (id integer    references myt(id));
insert into a values(2);
ERROR:  insert or update on table "a" violates foreign key constraint "a_id_fkey"
DETAIL:  Key (id)=(2) is not present in table "myt".


Regards,
Jayadevan
Reply | Threaded
Open this post in threaded view
|

Re: Inheritance and foreign keys

Andreas Kretschmer-3
Not possible - yet.

Am 25. Mai 2017 13:48:59 MESZ schrieb Jayadevan M <[hidden email]>:
Hi,

I designed three tables so that one table inherits another, and the third table references the parent table. If a record is inserted into the third table and the value does exist in the parent table indirectly, because it is present in the inherited table, I still get an error. 
Is some option available while creating the foreign key so that it will consider the data in the child tables also while doing a constraint validation?

create table myt(id serial primary key);
create table mytc (like myt);
alter table mytc inherit myt;
insert into myt values(1);
insert into mytc values(2);
 select * from myt;
 id 
----
  1
  2

create table a (id integer    references myt(id));
insert into a values(2);
ERROR:  insert or update on table "a" violates foreign key constraint "a_id_fkey"
DETAIL:  Key (id)=(2) is not present in table "myt".


Regards,
Jayadevan

--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.
Reply | Threaded
Open this post in threaded view
|

Re: Inheritance and foreign keys

Achilleas Mantzios
In reply to this post by Jayadevan
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_fk_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_accounting_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_accounting_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.

On 25/05/2017 14:48, Jayadevan M wrote:

> Hi,
>
> I designed three tables so that one table inherits another, and the third table references the parent table. If a record is inserted into the third table and the value does exist in the parent table
> indirectly, because it is present in the inherited table, I still get an error.
> Is some option available while creating the foreign key so that it will consider the data in the child tables also while doing a constraint validation?
>
> create table myt(id serial primary key);
> create table mytc (like myt);
> alter table mytc inherit myt;
> insert into myt values(1);
> insert into mytc values(2);
>  select * from myt;
>  id
> ----
>   1
>   2
>
> create table a (id integer    references myt(id));
> insert into a values(2);
> ERROR:  insert or update on table "a" violates foreign key constraint "a_id_fkey"
> DETAIL:  Key (id)=(2) is not present in table "myt".
>
>
> Regards,
> Jayadevan


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Inheritance and foreign keys

Jayadevan
On Thu, May 25, 2017 at 6:00 PM, Achilleas Mantzios <[hidden email]> 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_fk_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_accounting_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_accounting_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.