Обсуждение: [GENERAL] Inheritance and foreign keys
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
Not possible - yet.
--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.
Am 25. Mai 2017 13:48:59 MESZ schrieb Jayadevan M <maymala.jayadevan@gmail.com>:
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----12create 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.
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 triggerswhich 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_docsas 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,tmpUSING 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 tablesof 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 recordis 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 whiledoing 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
On Thu, May 25, 2017 at 6:00 PM, Achilleas Mantzios <achill@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_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.