Обсуждение: Inherit trouble
Hello Everyone,
I give you example of what's it likes.
I got 4 tables :
- order
- order_details
- product
- hard_drive
Hard_drive inherit product
order_details got order on foreign key
order_details got product on foreign key
When I try to add an order_details line, with a hard drive product_id, I got message "SQL Error : an instruction insert or update on the table order_details fail on the integrity constraint of the foreign key pk_product detail : the key (id)=(9) does not exists on the table product"
The product 9 is a hard drive, so it's inserted in the hard drive table, but as it inherits product, I see it in product table too.
Why do i Have this message ? Can you help me ?
Thank you
David
The product 9 is a hard drive, so it's inserted in the hard drive table, but as it inherits product, I see it in product table too.
Why do i Have this message ? Can you help me ?
Thank you
David
Working as designed; foreign keys and unique indexes are only valid for the explicit table they point to.
You can try making “hard drive” have a one-to-one relationship with “product” instead of inheriting from it. Either give “hard drive” its own PK and add a “Product PK” to the table or have the “hard drive” and “product” tables both use the same primary key (product PK) with a FK between them.
David J.
On Wed, Jul 6, 2011 at 3:13 PM, David Johnston <polobo@yahoo.com> wrote: > > The product 9 is a hard drive, so it's inserted in the hard drive table, but > as it inherits product, I see it in product table too. > > Why do i Have this message ? Can you help me ? First you need to have some way to differentiate what goes in the tables if they need to be partitioned that way. Then you can create an inheriting table for the fkey maps too and use rules to select which table a row goes into. Note: Use DO INSTEAD not DO ALSO rules. DO ALSO with inserts are big foot guns. Best Wishes, Chris Travers