I am writing DDD bounded contexts with some entities using event sourcing for their persistence and other entities using SQL databases.To simplify writing of the entities that use SQL persistence, given everything is controlled via the entity in the domain layer is it common practice to skip the notion of referential integrity against foreign keys? Can these integrity checks on foreign keys be excluded from the underlying databases entirely?If so this would reduce complexity in any ORM, and reduce the stores to standalone tables with the entity domain layer handling the relational mapping between.Are there any pitfalls against this approach? So far i cant see any when using entities to control everything.
Using SQL for DDD Persistence - Is Referential Integrity Mandatory?
domain driven design;repository
I briefly worked at a company where they decided to not add database integrity constraints on any of the tables because they were planning on relying on the ORM and correct programming to ensure the integrity of the database.To put it mildly it was a massive failure. Code mistakes had caused the database to lose integrity, and at the time I joined about 1/2 of development time was fixing bugs from data integrity problems that could have been avoided with proper database constraints.On top of that, trying to explore and understand the schema was very difficult. You could only guess at the columns that were intended to be foreign keys by the column's name and type. Schema diagramming tools don't work because they rely on foreign keys to map the relationships.Your programs may contain bugs, or you may misconfigure something, so you shouldn't rely on programs to maintain the integrity of your database. Also you may wish to connect tools directly to the database at some point, and those will be free to cause mayhem on your data integrity without constraints. If I were in your position I would rather be safe than have to fix data inconsistencies in the future.