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:
-
It's difficult to catch and respond to specific errors. Most times the DB layer throws a generic exception (
PDOExceptionfor PHP) with the actual error message as a string. It would be much easier, if, for instance, aUniqueConstraintViolationExceptionorForeignKeyConstraintViolationwere thrown. - 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.
Oldest comments (5)
Both. I use both when I use RDBMS.
You never know how your data is going to get into the DB, it might not always use the ORM layer. Also sometimes you might forget to enforce a certain rule and, typical situation, end up with null values in columns that are not supposed to have null values :D
I agree DB constraints can be a pain in the ass but if it happens a lot maybe there's an error in the app design?
👍 here. You can always find a way to skip the app layer, specially on the console for RoR, for example. Data constraints should be added to the DB first, and then as a sanity check, added to the app layer.
Databases are machines for organizing information. If you're treating them as dumb storage you might as well put your stuff in a pile of CSVs. Especially if you're trying to manage constraints outside the place where they matter most! Database constraints are law; application logic constraints are advice.
To your specific points:
What are you really going to do differently in client code if a save fails because of a unique constraint violation as opposed to a foreign key issue? The save has failed either way, and for most cases the specific "why" is only important for debugging.
Perfectly true; however, it's less a pain in the ass than having years' worth of inconsistent data because you're not enforcing referential integrity, and unless you're doing it in the database with foreign key constraints you're not enforcing referential integrity.
Thanks for sharing, Dian. Regarding point 1, I would absolutely hate to tell the user "Error: Couldn't save data" because I don't know the exact error. If the constraint was checked by the app, though, I would know the exact constraint that failed, and I can advise the user accordingly ("That username is taken").
I'm thinking it's best to enforce constraints in both places.
Yeah, you need validation on the request before it makes it to the database. What the user enters isn't always what the database stores anyway. It'd be nice if data layers could raise precise errors in a general enough format to make communicating them to the user easy in any scenario but that'd require a lot of stuff to line up just so.