Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Yeah. I've read up on a lot of the stuff InnoDB does (and have been using it in some test projects), but The Boss doesn't really approve of its use for whatever reason. I'm just trying to weight if it's worth waging a battle over or not. –  Xkeeper Aug 10 '11 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.