While it's not always good design to use triggers for this, sometimes, it's a valid reason to use them for integrity checking or enforcing. Having `on delete` triggers not run for some delete's is violating the principle of least surprise.
When feature break this way, people start to distrust them and best practices get adopted that discourage using them, killing the features all together.
Using a database that does not have this misfeature, both triggers and foreign keys are perfectly safe to use, but because the one database that is the most widely used has issues like the one described here, you often hear the recommendation to not use triggers or even foreign keys.
Yes, you can potentially move the logic normally contained in either of them into the application, but as you deal with more concurrency and as you start accumulating bugs, sooner or later your data will be messy and you will need to add cleanup-scripts, or, heaven forbid, work around messy data in your application logic (don't. do. that. it's the path to the dark side of unmaintainability).
Or, of course, use a database system whose features work correctly and the principle of least surprise is in effect.
PostgreSQL is one of these, by the way.
It makes apple's application simple stupid and efficient.
And they have triggers on foreign keys... with sqlite.
...
It is not postgres in one of it, it is much more mysql is not a correct rdbms. None of the other databases have this flaw.
Mysql is to RDBMS what mongo is to NoSQL, nowhere short of achieving anything that matters correctly.
EDIT: or better: Mysql is a realistic in the Hollywood way implementation of a RDBMS
postgres usually outperforms mysql for more complex operations, but is not as fast as mysql for simple ones.
sqlite is really only appropriate for things like mobile or desktop applications or things with generally low concurrency requirements.
i would be careful throwing the baby out with the bath water in your dismissal of mysql, but thats just my opinion:)
can you shed some more detail on that apple uses triggers with sqlite? that is interesting to me, and would like to learn more.
I agree with most of what you said, but in many many use cases data integrity can be maintained in application code (a statically typed system helps), it simply can't be true that all those applications running on NoSql database will have messy data and/or cleanup-scripts.
nosql or sql large platforms almost always have some amount of work always ongoing to clean, shuttle, and maintain data integrity.
that doesn't make me not a fan of them; for lower transaction rate applications ( which most probably are ), they are a small time investment to have some sanity checking. but it's a right tool for the job thing.
this is yet another reason why people dislike FKs in best practice stuff.
Edit: Hi down-voters. Curious to hear why I'm being down voted and possibly be offer a rebuttal?
https://bugs.mysql.com/search.php?search_for=&status=Active&...
That's all the active bugs against MySQL. Note that this 10 year old bug isn't the oldest.
This is the oldest, from March 2003: https://bugs.mysql.com/bug.php?id=199 Note someone submitted a fix for that over a year ago (and verified under the OCA in November) but it still hasn't shipped. This is a bug that should never have taken 11 years to fix.
https://bugs.mysql.com/bug.php?id=3052 This one is great. ROLLBACK in a stored procedure doesn't close the cursor.
MySQL, for all its strengths, often feels like there is a bunch of typical geeks running the show. Lots of focus on the 'sexy' new features, not so much focus on keeping the lights on.
We are currently looking into bug 199. The patch itself is probably too large to backport into a GA release.
- Triggers are at the SQL Layer
- Foreign keys are implemented natively by InnoDB at the storage engine layer
It is not as easy to fix as it sounds.
I guess every project as a few "we'll fix it some day" tickets.
MariaDB documents "foreign keys do not activate triggers" as the standard behavior:
https://mariadb.com/kb/en/mariadb/trigger-limitations/
Personally, I am grateful for software that changes as seldom as possible. I don't want to spend time on "updating" my application because something down in the stack changed.
Application can just not update, so there is no need for windows level of compatibility management
Personally never jumped on the mysql train and can say I'm really glad for that.
[30 Jun 2005 19:04] Dmitry Lenev We will fix this in 5.1
(For those interested, it's the ability to create a new, empty file on the server.)
One of the major reasons to pick open-source software over a proprietary software is you can fix bugs that the vendor or developers won't.
