On 2015-11-18 1:58 AM, Dominique Devienne wrote: > On Wed, Nov 18, 2015 at 10:17 AM, Darren Duncan <darren at darrenduncan.net> > wrote: > >> 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. > > [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.
I think its more considered an exception because it is typically hard to do so people don't try. But if arbitrary database constraints were easy, people would probably be used to them and make more use, so less exceptional. >> 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 While I agree that in the real world some things need to be in order, that isn't as true in the computer. Its all a matter of abstraction. In your analogy, one wants to create a file AND a directory; they should just be able to tell the system they want to create those 2 items as a single change, and have it happen, without having to worry about order. But more importantly, a relational database is different than a file system, and what I propose is more appropriate there. I'm talking about putting records in several tables at once using a single SQL statement, which is like creating a set of files only and not also directories, apples and oranges. That being said, your file/directory analogy is more like creating a table and populating it in one statement, which incidentally can be done with a CREATE TABLE AS SELECT statement, though combining actions on other tables into the same statement isn't provided by SQL. -- Darren Duncan
