Re: Foreign key constraint question

On Jul 20, 2007, at 9:06 AM, Michael Fuhr wrote:

> On Fri, Jul 20, 2007 at 08:57:25AM -0500, Perry Smith wrote:
>> I want to do something like this:
>>
>> ALTER TABLE companies ADD CONSTRAINT fk_companies_item_id
>> FOREIGN KEY (item_id, 'Company')
>> REFERENCES item_bases(item_id, item_type)
>> INITIALLY DEFERRED
>>
>> I could add a column to companies that is always set to "Company" but
>> that seems like a waste. I tried the above and I got a syntax error.
>
> What purpose is the constraint intended to achieve?

I am trying to create a polymorphic item. item_bases holds the
item_id and item_type. Different tables like companies, people, etc
will hold the data. This example is for companies. The item_type
does not hold the actual table name but holds the "class" name.

The idea is that the item_id will be the same in item_bases and in
companies (to pick an example). I want to make sure that the
item_base points to an item and the item points back to the item_base.

I can add a check constraint to item_base to make sure that with the
item_id and item_type it references a record in the right table. But
I can not defer that. So, I plan to add to the companies table
first, then add to item_bases table.

So, with the the foreign key constraint above, I want to make sure
that (item_id, item_type) of companies matches an (item_id,
item_type) in item_bases but in companies, I do not have an item_type
column since it will always be a constant.

Can I add a column to the table somehow like: "Company" as
item_type ? That would achieve the same effect.

Thank you,
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