Re: Foreign key constraint question

On Jul 20, 2007, at 7:01 PM, Jeff Davis wrote:

> On Fri, 2007-07-20 at 09:27 -0500, Perry Smith wrote:
>> 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.
>
> Is there a reason you're storing the type (or, to be precise, a more
> specific type) of the entity as a _value_ in the table that holds
> attributes of the super type?
>
> That seems limiting, and means you can only have one extra level of
> specificity in your entity type.
>
> Better to just not include the type of the entity as a value at
> all. If
> you want only companies, join item_bases to companies and the join
> will
> eliminate any non-company entities from the result (because the non-
> company entities in item_bases will have no matching tuple in
> companies).
>
> The relational model handles inheritance and polymorphism very well if
> you don't store types as values.

What if I have just an id for an item? This will happen when another
table references an item. How do I know what type it is? Are you
suggesting I look in companies, people, etc, etc to find the type?
It would seem better to have a table that tells me the type. Then
retrieve the item from the specified table.

>> 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.
>>
>
> You can use a constraint trigger. The current docs say that's "not
> intended for general use," but it will be available for general use in
> 8.3. And those can be deferred.

I saw those but where scared away from them because of the "not for
general use".

With my somewhat limited experience, I figured that applied to me.

Thank you again for your help,
Perry