Обсуждение: 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.