What I mean really is: when working with relational databases, and you have complex constraints to ensure (not trivial things like notnull but something like: "two tables away" uniqueness, etc..), do you like filling it with tricks using PKs/FKs/UNIQUEs to ensure integrity or do you prefer writing some code that will get executed as you application boots up, that ensures that all the more complex requirements are met?
Ask HN: What's your preferred way of ensuring complex database integrity?
1–10 of 58 posts
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#2Database triggers is a common tool to use in such cases
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#3Generally my rule of thumb is to keep the integrity checking as close to the data as possible. Usually that means integrating it into the schema/database (optionally with triggers), as long as that has proper tooling (versioning specifically).
With more complex checks it might be necessary to build a periodically runnable check mechanism. At startup might be a good way to do it, or some sort of cronjob. I can imagine that in larger systems there might even be separate services to check data integrity across other services.
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#4As mentioned by the other comment, it's standard to use database triggers to ensure consistency.
There are two major problems with dealing with them in the application side
1. You can no longer guarantee consistency.
2. In the case where you need to do some things procedurally so that your application can no longer make a yes/no query to determine if a constraint is violated but need to make use of intermediate results, then this means that you will need to transfer intermediate results over the wire, which is a performance penalty over just doing it in the database server.
edit: of course, when you need to ensure consistency with some data source outside the database, there's no choice but to do it application-side.
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#5Integrity checks during startup will slow down in time. Also, does it mean you have to restart the app often? Sounds bad to me.
Complex constraints sounds like an app/domain responsibility, much easier to test, version, troubleshoot, evolve.
When a solution cannot quickly and easily be tested, it impacts overall agility (small iterations become small waterfalls). Having complex constraints in database, could mean slower release cycles, processes (schema changes reviews, etc), more migrations.
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#6triggers.
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#7If you're using SQL Server, you can use indexed (materialized) views to enforce uniqueness across multiple tables.
There's also a neat little trick with indexed views and cross join[1] that you can use for more complex constraints. I've never used it for anything more than reference tables, so I can't comment on its ability to scale.
[1] https://spaghettidba.com/2011/08/03/enforcing-complex-constr...
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#8Isn't that the primary purpose of transactions?
I mean, every time you write to the database, you open a transaction, do you stuff and close it. It either works or it doesn't. If it doesn't, then it rolls back. Isn't that enough?
I guess I am saying that for complex integrity checks, it should be the responsibility of the application's data layer.
On the other hand what is meant by "complex"? Is it "complex" because of numerous, ever-changing applications that touch the database or is it just one application and a database with a lot of tables?
In the case of wild, numerous applications, I suppose that database-side integrity controls are needed. If you really have ONE application touching the database, it might be better to have the application police integrity. Perhaps there's more than one answer?
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#9I much prefer when the database enforces integrity by limiting access and preventing corruption from occurring.
In Microsoft SQL Server, you can often achieve the desired result with check constraints, primary and foreign keys, indexed views, and unique indexes. When more logic is required, I usually restrict write access from the application at the table or column level and force modifications to occur through a stored procedure that enforces integrity.
Re: Ask HN: What's your preferred way of ensuring complex database integrity?
#10I try to encode all constraints in the database using constraints and triggers. This way your data is always consistent regardless of which app/language is interacting with it.
Sometimes I add the same or similar constraints in the application layer because it leads to better error/validation messages.
IMO having data integrity enforced in the database instead of application code is similar to having a type system enforcing code integrity at compile time instead of at runtime. It's about catching errors as early and as close to the root as possible.