4 ms·
Foreign keys allow for pushing a really vital piece of business logic down to the database itself, referential integrity. This can be done in application logic
by _heimdall 3y ago
Foreign keys allow for pushing a really vital piece of business logic down to the database itself, referential integrity.
This can be done in application logic, but that risks bugs allowing broken references into your data. Its more foolproof when these checks are enforced directly at the db, making sure data is valid before it's stored or updated.
- kevincox 3y agoNo to mention that most SQL databases have complex consistency rules. Just checking that the target row exists (or not) before adding (or deleting) a record may not result in a correct database state in the face of concurrent transactions. You better very carefully read your database's concurrency guarantees and ensure that all transactions are running with the right consistency level otherwise you will have a bad day. On top of that the JOIN method of following foreign keys often leads to missing rows if expected keys don't exist which can be very hard to debug.
- PartiallyTyped 3y agoThere was an interesting topic on r/experienceddevs [1], where the dev team was arguing with some DBAs on adding another column. The DBA insisted on using FKs over enum / application level logic, etc. The comments there present some excellent arguments on top of everything you said. [1] https://www.reddit.com/r/ExperiencedDevs/comments/18ldexi/wrestling_with_dbas_every_time_we_need_to_add_a/ https://www.reddit.com/r/ExperiencedDevs/comments/18ldexi/wr...