inheritance help

[Jim McNeely]

I'm trying some experiments with PostgreSQL 7.3.4 with inheritance.

What I read is that if you implement triggers on a parent table, those
same triggers won't fire if you add records to a child table. However,
I tried it, and they did indeed fire, and worked perfectly. Same
sources said that indexes (indices??) wouldn't work right. Is that true?

What I'm trying to do is make the primary key id field reside in a base
table, which basically all the tables inherit. That way every row in
the database gets a unique id, and I only have to implement the key,
and the mod time/user creation time/user fields once.

I'm just wondering if anyone has been down the road with this, and can
tell me what might be wrong with this kind of a structure.

TIA

Jim McNeely
New Century Data
ji*@newcenturydata.com
http://www.newcenturydata.com
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jonathan Bartlett]

Here's the way I handle it - and it works in other DBs as well:

create sequence objects;

create table X (object_id int8 not null default nextval('objects') primary
key, ......)

create table Y (object_id int8 not null default nextval('objects') primary
key, .......)

create table lastmods (object int8 not null, created_by int8, created_date
timestamp, last_modified_by int8, last_modified_date int8);

You can use object as a foreign key to all of your tables. Unfortunately,
unless you add several triggers and another table, you can't get foreign
key constraints.

Jon

On Tue, 25 Nov 2003, Jim McNeely wrote:

I'm trying some experiments with PostgreSQL 7.3.4 with inheritance.

What I read is that if you implement triggers on a parent table, those
same triggers won't fire if you add records to a child table. However,
I tried it, and they did indeed fire, and worked perfectly. Same
sources said that indexes (indices??) wouldn't work right. Is that true?

What I'm trying to do is make the primary key id field reside in a base
table, which basically all the tables inherit. That way every row in
the database gets a unique id, and I only have to implement the key,
and the mod time/user creation time/user fields once.

I'm just wondering if anyone has been down the road with this, and can
tell me what might be wrong with this kind of a structure.

TIA

Jim McNeely
New Century Data
ji*@newcenturydata.com
http://www.newcenturydata.com
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org