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
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 |