It isn't really clear to me how this differs from usual database integrity constraints? However, I'm not familiar with what typical SQL databases provide.
The database I work on has had sophisticated integrity declarative integrity constraints for a decade now, so it seems surprising that this would be considered something new.
Integrity (consistency) was the biggest point of relational database theory itself.
> The true importance of relational theory is highlighted by the title of the original (and considerably shorter) version of Codd’s first paper. That version predated the published version by a year, and the title was “Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks.”[1] The title of this unpublished version emphasizes that the real importance of relational theory is that it provides a rigorous method of asserting arbitrarily complex consistency constraints that must be satisfied by the data within the database.
Interesting, which DB is that? The ones i checked only have constraints on one table at a time (besides foreign keys).
The idea here is that constraints could span across multiple tables via chains of foreign keys. It might not be a new idea but i haven't seen any implementations yet (unless you count triggers).
If you wanted to express that for a transfer involving more than 10,000 dollars that it must have been approved by the user's manager. Basically any logical formula can be used as a constraint. It's also possible to express basic temporal constraints like
Thanks! I will check Datalog out, i have never used it (though i know Prolog a bit).
So the main difference is then that my version is SQL based:
alter table transactions add constraint c_amount check (amount > 10000 -> auth.name = user.man.name) # or something similar, where you refer to other tables via a chain of foreign keys.
I'm not sure why you need to restrict constraints between tables to be based on foreign keys. I guess maybe that is a limitation inherent to SQL?
We also allow constraints over aggregations. Though our syntax does require giving the result of the aggregation a temporary "table" name so that it can be used as part of the constraint.
That's a shame we have triggers as the only tool for this job, because e.g. CHECK constraint "cannot contain subqueries nor refer to variables other than columns of the current row"[1]. Triggers are very low-level thing actually, I'd consider them an assembler of RDBMS. So the questions like "Do the triggers and constraints outlined below actually cover all the bases, or is it still possible to add/modify data in such a way that the result would be inconsistent?"[2] are raised too often (usually they are not raised at all, and that's even worse). And the obvious answer[3] is application-level consistency checking - which is exactly me personally consider a shame for current state of RDBMS, especially in the light of Codd' ideas (referenced above), considering the more than half of century history of RDBMS industry.
Yes they are similar but they are procedural, i'm trying to do a declarative thing. I want to eliminate the need to figure out what constraint can a specific insert/delete on a specific table can break exactly.
It's not clear to me why this would get you faster development cycles or better performance than application-level checks? And I'm not really sure what "dependency tracking" is supposed to mean?
Yes you're totally right i need to write the readme properly. You get faster development cycle by skipping the app layer altogether. It's a few sql statements vs java code/release/deploy. Performance: you don't need multiple queries for a constraint plus you can optimize from info within the DB. Dependency tracking: no need to figure out yourself which table/operation can break which constraint.
The database I work on has had sophisticated integrity declarative integrity constraints for a decade now, so it seems surprising that this would be considered something new.