Search code examples
language-agnosticdesign-patternsormdomain-model

Should we use foreign-key constraints when persisting Domain Models?

A while ago I had a discussion with my colleagues about the persistence of Domain Models and whether we should enforce foreign-key constraints at the database level.

My first reaction was that the very use of a relational database implied the enforcement of such constraints, but some argued that the database should be seen as nothing but a persistence mechanism and therefore we should avoid placing any business logic in it. We ended up by not using foreign-key constraints.

Here is my question (I hope it is not too generic): is it considered good practice to enforce key constraints in theses cases?

Solution

  • Enforce constraints, but do NOT rely on them in your business logic

    • No business logic on the database: I agree with the principle. And if your non-SQL business code relies on the database constraints to check your database consistency, then you should rethink your business logic.
    • There is nothing wrong of having database constraints in addition to your business logic. Especially because things like referential integrity with FOREIGN KEYs and other UNIQUE constraints are easy to do and RDBMS are doing that job for you very efficiently without much maintenance.
    • Will you not use indices on the database too, because it is not purely persistency related?
    • Finding and fixing software bug may take you some time, but you definitely do not want to spend even more time cleaning up or (worse) loosing some data, just because you saved yourself a trouble of writing one-line script for a FK. Really: your get something for free here and your reject it?
    • [EDIT-1]: can you guarantee that the data in your database would be managed ONLY via your application? There always seem to be exceptions, mostly by power-users, who do sometimes (very rarely :-) make mistakes and execute some SQL statements to clean-up your code, update statuses (to invalid values because of typos) etc.
    • [EDIT-2]: Building domain driven model is not an excuse not to hire a good DB admin. Using ORM is not an excuse not to hire good DB developer.

    But if you and your team are able to write bug-free software and handle all possible exception scenarios in your code (including hardware/network/dummy-user/programmer-error failures), then "Hei, why bother with redundant FK constraints...." - -teaser-