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
> jim@newcenturydata.com
> http://www.newcenturydata.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>