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@xxxxxxxxxxxxxxxx ) Ease Software, Inc. ( http://www.easesoftware.com ) Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems
