Re: Foreign key constraint question
От | Perry Smith |
---|---|
Тема | Re: Foreign key constraint question |
Дата | |
Msg-id | E3F45C84-507A-4615-BE63-BD1D400FAACE@easesoftware.com обсуждение исходный текст |
Ответ на | Re: Foreign key constraint question (Jeff Davis <pgsql@j-davis.com>) |
Ответы |
Re: Foreign key constraint question
(Jeff Davis <pgsql@j-davis.com>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: