inheritance and primary/foreign keys

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