Thread: inheritance and primary/foreign keys
Question 1: If table A has as its primary key "a_pk" and table B
inherits table A, then table B also has as its primary key a_pk. Is
that correct?
Question 2: If I want table C to have a foreign key on both A and B, is
the following syntax correct? (I'm using 7.0.3)
CREATE TABLE c (
c_fk correct_type REFERENCES a*(a_pk)
);
(The question is, Do I need the *?)
Question 3: I understand that the default action on this will reverse in
7.1 (i.e. the default will then be to reference all tables unless ONLY
is specified). Am I correct in assuming that the dump/restore (required
for upgrading) will take care of this, or will I need to recreate table
C manually removing the *?
Thanks for your help.
--
Dan
____ Kressin ____ .-----------------------------------.
/___ \____________/ __ \ | Unix SysAdmin |
\ \ / / \ | | Global Crossing |
___/ __\/\/rench_ \__/ | | dkressin@globalcrossing.com |
\____/ \____/ | http://www.vib.org/wrench/ |
`-----------------------------------'
On Wed, 7 Mar 2001, Daniel J. Kressin wrote: > Question 1: If table A has as its primary key "a_pk" and table B > inherits table A, then table B also has as its primary key a_pk. Is > that correct? I don't believe so currently. > Question 2: If I want table C to have a foreign key on both A and B, is > the following syntax correct? (I'm using 7.0.3) > CREATE TABLE c ( > c_fk correct_type REFERENCES a*(a_pk) > ); > (The question is, Do I need the *?) > > Question 3: I understand that the default action on this will reverse in > 7.1 (i.e. the default will then be to reference all tables unless ONLY > is specified). Am I correct in assuming that the dump/restore (required > for upgrading) will take care of this, or will I need to recreate table > C manually removing the *? You cannot safely reference tops of inheritance trees under 7.0 or 7.1 and have it reference the trees. Which reminds me, the fk constraint triggers should probably specify ONLY on their queries or they'll fail strangely under 7.1.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Which reminds me, the fk constraint triggers should probably specify ONLY
> on their queries or they'll fail strangely under 7.1.
Looks like this was fixed awhile ago...
regards, tom lane
Hi all, I want to read shell environment variable into pl/psql function, like $HOME or $APP_USER. Does anyone know a simple way to do that ? Or should I have to read the environment file line by line ? Thanks in advance. Regards, Gilles DAROLD
On Wed, 7 Mar 2001, Stephan Szabo wrote:
>
> You cannot safely reference tops of inheritance trees under 7.0 or 7.1 and
> have it reference the trees.
>
Is there anyway to emulate this? I want to have several types of persons
which have varied and common data. How can I implement refences to a
person or a child table of person? Are there any problems with the parent
having a serial column? The index should be unique for all the children
too.
CRETE TABLE person (
id SERIAL,
fname varchar(15),
lname varchar(15),
pwd varchar(8)
);
CREATE TABLE teacher (
uname char(4)
) INHERITS (person);
CREATE TABLE admin (
uname char(8),
type integer
) INHERITS (person);
CREATE TABLE record (
id SERIAL,
pid integer REFERENCES person
);
- Einar Karttunen
On Wed, Mar 07, 2001 at 03:54:02PM -0800, Stephan Szabo wrote: > On Wed, 7 Mar 2001, Daniel J. Kressin wrote: > > > Question 1: If table A has as its primary key "a_pk" and table B > > inherits table A, then table B also has as its primary key a_pk. Is > > that correct? > > I don't believe so currently. > > > Question 2: If I want table C to have a foreign key on both A and B, is > > the following syntax correct? (I'm using 7.0.3) > > CREATE TABLE c ( > > c_fk correct_type REFERENCES a*(a_pk) > > ); > > (The question is, Do I need the *?) > > > > Question 3: I understand that the default action on this will reverse in > > 7.1 (i.e. the default will then be to reference all tables unless ONLY > > is specified). Am I correct in assuming that the dump/restore (required > > for upgrading) will take care of this, or will I need to recreate table > > C manually removing the *? > > You cannot safely reference tops of inheritance trees under 7.0 or 7.1 and > have it reference the trees. > > Which reminds me, the fk constraint triggers should probably specify ONLY > on their queries or they'll fail strangely under 7.1. Can someone give a good use for this inheritance thing? I've never been able to come up with a scenario where it makes sense. It always seems more problematic than just using multiple related tables. -- Eric G. Miller <egm2@jps.net>
On Fri, 9 Mar 2001, Einar Karttunen wrote: > On Wed, 7 Mar 2001, Stephan Szabo wrote: > > > > You cannot safely reference tops of inheritance trees under 7.0 or 7.1 and > > have it reference the trees. > > > Is there anyway to emulate this? I want to have several types of persons Someone's been trying to get a workaround. Hopefully if they get it done, they'll post it to the list. However it's still fairly ugly.
On Fri, 9 Mar 2001 19:34:31 +0000 (UTC), Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > >On Fri, 9 Mar 2001, Einar Karttunen wrote: > >> On Wed, 7 Mar 2001, Stephan Szabo wrote: >> > >> > You cannot safely reference tops of inheritance trees under 7.0 or 7.1 and >> > have it reference the trees. >> > >> Is there anyway to emulate this? I want to have several types of persons > >Someone's been trying to get a workaround. Hopefully if they get it done, >they'll post it to the list. However it's still fairly ugly. > I was working on something very similar to this. What I did was have all of the records get serial numbers from the same sequence, and use a function ( "is_person" ) to tell whether or not the id was valid. I went back and forth about making these tables use inheritance. Really the only thing I wanted was to have unique "person_id"s for everyone and I don't think it is necessary to use the actual inheritance mechanism to implement this. What other advantages are there to using inheritance?