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
