question regarding REFERENCES and INHERITS

From: Beatrice Yueksel <beatrice(at)msys(dot)ch>
To: pgsql-admin(at)postgresql(dot)org
Subject: question regarding REFERENCES and INHERITS
Date: 2003-03-31 10:06:26
Message-ID: 3E881322.7050403@msys.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Robert Treat 2003-03-31 13:38:36 Re: Requests per second ?
Previous Message Kevin Brown 2003-03-31 09:10:45 Re: [pgsql-advocacy] uppercase = lowercase