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. 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. 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. -- Darren Duncan
