Обсуждение: question regarding REFERENCES and INHERITS
Hi, I made some test with REFERENCES and INHERITS. I know from the mailing list that : "Referential integrity only applies to the named table and not any child tables..." I have 3 tables: art, schiff_admin (for administration with rules, view, etc...) schiff (inherits schiff_admin) There is one reference from schiff_admin to art. The reference doesn't work in schiff. If I add a constraint: ALTER TABLE schiff_admin ADD CONSTRAINT schiff_admin_fk_art FOREIGN KEY(art) REFERENCES schiff_art (art) ON DELETE NO ACTION ; the reference works also in schiff. Why the reference is inherited after the "alter table" and not before? Thank you in advance, Beatrice ____________________ First step. ____________________ Create sequence schiff_id_seq; Create table schiff_art ( art VARCHAR(4) PRIMARY KEY NOT NULL, bezeichnung VARCHAR(50) NOT NULL ); Create table schiff_admin ( schiff_id INTEGER DEFAULT nextval('schiff_id_seq') PRIMARY KEY NOT NULL, art VARCHAR(4) REFERENCES schiff_art, schiffsname VARCHAR(255) NOT NULL ) ; Create table schiff () INHERITS (schiff_admin); \d schiff_admin; Table "public.schiff_admin" Column | Type | Modifiers -------------+------------------------+------------------------------------------------- schiff_id | integer | not null default nextval('schiff_id_seq'::text) art | character varying(4) | schiffsname | character varying(255) | not null Indexes: schiff_admin_pkey primary key btree (schiff_id) Foreign Key constraints: $1 FOREIGN KEY (art) REFERENCES schiff_art(art) ON UPDATE NO ACTION ON DELETE NO ACTION \d schiff Table "public.schiff" Column | Type | Modifiers -------------+------------------------+------------------------------------------------- schiff_id | integer | not null default nextval('schiff_id_seq'::text) art | character varying(4) | schiffsname | character varying(255) | not null ____________________ Second step. ____________________ ALTER TABLE schiff_admin ADD CONSTRAINT schiff_admin_fk_art FOREIGN KEY(art) REFERENCES schiff_art (art) ON DELETE NO ACTION ; \d schiff_admin; Table "public.schiff_admin" Column | Type | Modifiers -------------+------------------------+------------------------------------------------- schiff_id | integer | not null default nextval('schiff_id_seq'::text) art | character varying(4) | schiffsname | character varying(255) | not null Indexes: schiff_admin_pkey primary key btree (schiff_id) Foreign Key constraints: $1 FOREIGN KEY (art) REFERENCES schiff_art(art) ON UPDATE NO ACTION ON DELETE NO ACTION, schiff_admin_fk_art FOREIGN KEY (art) REFERENCES schiff_art(art) ON UPDATE NO ACTION ON DELETE NO ACTION \d schiff; Table "public.schiff" Column | Type | Modifiers -------------+------------------------+------------------------------------------------- schiff_id | integer | not null default nextval('schiff_id_seq'::text) art | character varying(4) | schiffsname | character varying(255) | not null Foreign Key constraints: schiff_admin_fk_art FOREIGN KEY (art) REFERENCES schiff_art(art) ON UPDATE NO ACTION ON DELETE NO ACTION
On Mon, 31 Mar 2003, Beatrice Yueksel wrote: > Hi, > I made some test with REFERENCES and INHERITS. > I know from the mailing list that : > "Referential integrity only applies to the named table and not > any child tables..." As a note, this phrase was intended to apply to the target referenced table (schiff_art in this case). > I have 3 tables: > art, > schiff_admin (for administration with rules, view, etc...) > schiff (inherits schiff_admin) > > There is one reference from schiff_admin to art. > The reference doesn't work in schiff. > > If I add a constraint: > > ALTER TABLE schiff_admin > ADD CONSTRAINT schiff_admin_fk_art > FOREIGN KEY(art) > REFERENCES > schiff_art (art) ON DELETE NO ACTION ; > > the reference works also in schiff. > > Why the reference is inherited after the "alter table" and not before? Because inheritance is wierd right now :) Seriously, the alter table is appearing to recurse the tree (as of the time the constraint is made), however create table inherits doesn't make the triggers. With pg_constraint in place, that might actually not be as hard now.