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 ( [EMAIL PROTECTED] )
Ease Software, Inc. ( http://www.easesoftware.com )
Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
