I used to be a big believer in using SQL constraints to ensure data integrity. I'm talking primarily about constraints like FOREIGN KEY to manage relationships between models and UNIQUE to ensure uniqueness. Now I'm wondering whether it's better to enforce such in the app logic itself, via ORM and validators.

My biggest gripes with SQL constraints are:

1. It's difficult to catch and respond to specific errors. Most times the DB layer throws a generic exception (PDOException for PHP) with the actual error message as a string. It would be much easier, if, for instance, a UniqueConstraintViolationException or ForeignKeyConstraintViolation were thrown.
2. When running manual tests that involve manipulating the database, sometimes database constraints can really be a pain in the ass.

Of recent, I've been leaning more towards enforcing my relations in the app logic. I still keep UNIQUE constraints on the DB, but I do a manual check first for duplicates.

Please share your thoughts and experiences.

I write about my software engineering learnings and experiments. Stay updated with Tentacle: tntcl.app/blog.shalvah.me.