On Wed, Nov 18, 2015 at 10:17 AM, Darren Duncan <darren at darrenduncan.net> wrote:
> On 2015-11-18 12:50 AM, Yuri wrote: > >> On 11/18/2015 00:45, Dominique Devienne wrote: >> >>> True. But that's in the case where FK constraints validation is deferred >>> to >>> the transaction end. >>> >> >> Why does SQLite defer constraint violation errors? Is there a benefit? >> This only complicates things, I would rather see the statement fail >> immediately. >> > > Deferred constraints are definitely a benefit. > [DD] In some cases, definitely. But in all cases, that's hardly true IMHO. > They allow you to express constraints otherwise not possible, for example > that a record may exist in table X if and only if a counterpart exists in > table Y, such as balancing records in a double-entry accounting system. > Granted all you really need for this is the ability to change multiple > tables as a single atomic operation, but failing that ability, deferred > constraints are the way SQL provides to do it. > [DD] Once again, those cases are more the exception than the norm. Deferred constraints are only "required" when faced with circular FKs, since the work-around of using an appropriate order for your statements works for all other cases. > Also, having to enter records in a specific order, eg parent/child, is a > contrivance given you're dealing with what should be a set-oriented > database, and a contrivance that can make using the database more > difficult. Ideally you just insert all the records in a change set at > once, and only the total effect of the change set is what is important for > enforcing constraints. SQL immediate constraints break this ease of use. [DD] That's ease of use you pay dearly for in terms of usability though. And that order-agnostic benefit you claim is IMHO "artificial" and more a "mathematical" concept than a physical reality. As an analogy, it's a bit like asking to create a file first in a non-existent directory, and later create that missing directly. The real world often requires to do things in a specific order, and schemas do model the real world most times, so being order dependent seems entirely "natural" to me. My $0.02. --DD
