Re: Foreign key constraint question

On Jul 22, 2007, at 10:35 AM, Alvaro Herrera wrote:

> Nis Jørgensen wrote:
>> Alvaro Herrera skrev:
>>> Nis Jørgensen wrote:
>>>
>>>> What if, for instance, I want to render a list of shapes?
>>>>
>>>> To render the shape, I need to get its data, to get its data, I
>>>> need to know what type it is. ISTM that the easiest way to achieve
>>>> this is storing the type info at the "top" of the table hierarchy.
>>>
>>> Try adding "tableoid" to the list of columns retrieved. Even
>>> better,
>>> cast that to regclass.
>>
>> This is if I use PG table inheritance, right?
>
> Right, that was what I was thinking.

Right now, I am pretty happy with what I have (but I do have some
extra data).

In what I call my item_base table, I have an item_id and item_type
(integer and string). The type is not the table name but a Class
name. I also have a table that goes from one to the other.

In each of my child (sub-class) tables I have the same tuple. But,
for example, in the companies table, I have an added constraint that
it is set to "Company". I have a "initially deferred" foreign
constraint of item_id and item_type from the child tables to the
item_base table. The item_base table has a check constraint that
translates the item_type to a table name and then verifies that the
id in the referenced table exists. I also have "on delete cascade"
set so they both disappear at the same time.

I do not have item_id in the item_base table of type serial. Instead
I have a sequence number that the child tables get their id from (all
from the same sequence). Then this id is put into the item_base
table. This is because the child table entry must be created first,
then the base because I can not defer the check constraint (which
turns out not to be true but I didn't know that when I started down
this road).

Thank you all for helping,
Perry Smith ( pedz(at)easesoftware(dot)com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems