Thread: Inheritence and Integrity

I am creating a database that will keep track of several different types 
of 'events'. I am toying with the idea of making a base 'class' table 
for the tables because a lot of the information will be the same (also 
there will probably be times I just need to get the basic information 
about events regardless of their type). My question is: will triggers 
and rules on the parent table fire when I insert data in the child 
tables? Are there any other potential pitfalls?

Thank you,
-Neal Lindsay

P.S. Here is a simplified example of my schema:

CREATE TABLE parenttable (recordid SERIAL PRIMARY KEY,recordname text
);

CREATE TABLE childtablea (afield int4
) INHERITS parenttable;

CREATE TABLE childtableb (bfield text
) INHERITS parenttable;

CREATE TABLE extrainfo (extrainfoid SERIAL PRIMARY KEY,record_fkey int4 NOT NULL REFERENCES
parenttable(recordid),extrainfotexttext
 
);

On Wed, 29 Jan 2003, Neal Lindsay wrote:

> I am creating a database that will keep track of several different types
> of 'events'. I am toying with the idea of making a base 'class' table
> for the tables because a lot of the information will be the same (also
> there will probably be times I just need to get the basic information
> about events regardless of their type). My question is: will triggers
> and rules on the parent table fire when I insert data in the child
> tables? Are there any other potential pitfalls?

Currently that won't do what you want because triggers are not inherited
and the constraint is set up so the references constraint ends up being
only on the rows in parenttable.  In addition, the primary key constraint
won't do what you probably want either, although since it's a serial, you
won't be likely to notice.

> P.S. Here is a simplified example of my schema:
>
> CREATE TABLE parenttable (
>     recordid SERIAL PRIMARY KEY,
>     recordname text
> );
>
> CREATE TABLE childtablea (
>     afield int4
> ) INHERITS parenttable;
>
> CREATE TABLE childtableb (
>     bfield text
> ) INHERITS parenttable;
>
> CREATE TABLE extrainfo (
>     extrainfoid SERIAL PRIMARY KEY,
>     record_fkey int4 NOT NULL REFERENCES parenttable(recordid),
>     extrainfotext text
> );

Stephan Szabo wrote:
> On Wed, 29 Jan 2003, Neal Lindsay wrote:
>>I am creating a database that will keep track of several different types
>>of 'events'. I am toying with the idea of making a base 'class' table
>>for the tables because a lot of the information will be the same (also
>>there will probably be times I just need to get the basic information
>>about events regardless of their type). My question is: will triggers
>>and rules on the parent table fire when I insert data in the child
>>tables? Are there any other potential pitfalls?
> 
> 
> Currently that won't do what you want because triggers are not inherited
> and the constraint is set up so the references constraint ends up being
> only on the rows in parenttable.  In addition, the primary key constraint
> won't do what you probably want either, although since it's a serial, you
> won't be likely to notice.
> 

So what you're saying is that I could insert a duplicate primary key 
into the parent table by inserting an explicit value in that field in my 
child table? And if I leave that column out of my insert statement the 
"default nextval()" will still make it a unique value?

If that is so, is there a way to make constraint that will keep primary 
keys unique across all the child tables of my parent table?

-Neal Lindsay

On Wed, 29 Jan 2003, Neal Lindsay wrote:

> Stephan Szabo wrote:
> > On Wed, 29 Jan 2003, Neal Lindsay wrote:
> >>I am creating a database that will keep track of several different types
> >>of 'events'. I am toying with the idea of making a base 'class' table
> >>for the tables because a lot of the information will be the same (also
> >>there will probably be times I just need to get the basic information
> >>about events regardless of their type). My question is: will triggers
> >>and rules on the parent table fire when I insert data in the child
> >>tables? Are there any other potential pitfalls?
> >
> >
> > Currently that won't do what you want because triggers are not inherited
> > and the constraint is set up so the references constraint ends up being
> > only on the rows in parenttable.  In addition, the primary key constraint
> > won't do what you probably want either, although since it's a serial, you
> > won't be likely to notice.
> >
>
> So what you're saying is that I could insert a duplicate primary key
> into the parent table by inserting an explicit value in that field in my
> child table? And if I leave that column out of my insert statement the
> "default nextval()" will still make it a unique value?

Well, the children have no unique constraint at all as defined.  But
if you pretty much only ever insert using the default it's fine since the
nextval() will give unique values.

> If that is so, is there a way to make constraint that will keep primary
> keys unique across all the child tables of my parent table?

I don't know of an easy way.  Possibly some kind of ugly trigger thing.

> inheriting pk and triggers

pg inheritance is quite limited.  what i (and i'm sure many others)
have done is:

1. create master sequence
2. create base table
3. create base trigger procedures
4. create derived tables, using "inherit"
5. write procedure p( table_name ) thata) sets pk of table_name using master sequenceb) attaches base trigger
proceduresonto table_name
 
6. run procedure p() against each derived table


another way to skin this cat is to use "objects" in the database:

-- base table
table common( int id primary key ..., ref_tab name,  -- name of secondary table using common ...            -- common
columnsand constraints
 
) without oids;

-- secondary table
table secondary1( int id1 not null references common(id), int id2 primary key,  -- (can use id1 as pk!) ...
     -- secondary columns and constraints
 
) without oids;

-- views for secondary table - generate!
create secondary1_v1 as select c.*, s.*
from secondary1 s join common c on( s.id1 = c.id );

-- (if you want) dml for view to make life easier - generate!
...

if you are maintaining the common info, or if you want a many to one
secondary to master, this approach is easier.


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com