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.
> 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.
Regards,
Jeff Davis