4

I've been working on a "legacy" schema in MySQL Workbench that uses a lot of relations. All of the tables are MyISAM, however, and there are no foreign keys.

However, I'm working on a new area of the schema now, and am wondering:

Would it be a good idea to force InnoDB and foreign keys for their ON UPDATE/ON DELETE triggers, or to implement the cascading logic in the application itself?

My super/the CTO seems to be remarkably inflexible when it comes to migrating to InnoDB/FKs (or using a real database construction program instead of phpMyAdmin) and I'm not sure if the gains for having auto-updating would be worth fighting over.

Probably the worst part about this issue is that if I went the application-side route I'd have to add code outside of the specific module, which would probably involve more issues and difficulty -- the system is already pretty complex as it is.

Xkeeper
  • 185
  • 5

1 Answers1

2

It is better to let InnoDB enforce foreign keys. Trying to implement referential integrity in an application can be brittle and would obligate you to write

  • rollback operations
  • check constraints on keys
  • manual cascading of DELETEs and perhaps UPDATEs

InnoDB is equipped for all that and more. Plus, operations done in application logic is difficult to rollback, especially if one uses stored procedures or user-defined triggers.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536