question regarding REFERENCES and INHERITS

Поиск
Список
Период
Сортировка
От Beatrice Yueksel
Тема question regarding REFERENCES and INHERITS
Дата
Msg-id 3E881322.7050403@msys.ch
обсуждение исходный текст
Ответы Re: question regarding REFERENCES and INHERITS  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-admin
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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Daniel Rubio
Дата:
Сообщение: Sizes for all databases
Следующее
От: Andrew Biagioni
Дата:
Сообщение: Re: pgAdmin II questions