1
ALTER TABLE `phppos_items_taxes`
ADD CONSTRAINT `phppos_items_taxes_ibfk_1` 
FOREIGN KEY (`item_id`) 
REFERENCES `phppos_items` (`item_id`) 
ON DELETE CASCADE;

Does this mean when phppos_items.id is deleted, it will be delete entries on phppos_items_taxes?

I am just have a hard time interpreting it.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Chris Muench
  • 17,444
  • 70
  • 209
  • 362

1 Answers1

1

yes, but do you really need to physically delete that entry. Sometimes it is better to set some kind of flag in root entity which will tell if it should act as deleted/filtered

Milan Jaric
  • 5,556
  • 2
  • 26
  • 34
  • because? If this were the case, you would use `ON CASCADE RESTRICT` and set the flag in the other table instead. If someone actively set the deletion, obviously not the op, then there is likely a reason why it is safe to fully delete. – Kevin Peno Mar 30 '11 at 22:44
  • because of reporting analytic data, imagine for instance you are creating orders and you have table product. Now orders are in same time invoices! it is very important to keep referenced data, like produc.code and name to avoid data redundancy. And what is product is no longer available for ordering. What, you will delete product for good??? I don't think so ;) You still need that name and code in invoices. Btw, in many ORMs you don't have ability to play so easy with DB engine specific features. I don't want My application to be so coupled to specific db engine – Milan Jaric Jul 09 '11 at 14:11
  • 1
    Foreign keys are not DB specific. Most ORMs support SQL8 standard, of which CASCADE is a part of. I'm not sure where you got the idea that they are DB specific. In either case, you should/would use `RESTRICT` even if you were setting a flag instead to prevent someone unfamiliar with your system from deleting the ID from the parent table while "archived" orders still exist. – Kevin Peno Jul 12 '11 at 22:29
  • You obviously don't see the point. Deleting records means it will never ever be accessible as it was not ever been inserted in database. If I for instance try to delete product from products table but cause of restriction I made to orders table I will not be able to do so. Now tell me what do you propose me to do? – Milan Jaric Jul 26 '11 at 08:00
  • In that very specific case you would need both `RESTRICT` and an active field on the product. `DELETE`ing a product in this case would mean that you would no longer want to know anything about it (the product, orders, or anything else associated with it). Thus you SHOULD be deleting the associated, more important, records first (namely orders). – Kevin Peno Jul 28 '11 at 16:48