Of the change set is what is important for enforcing constraints. Just insert all the records in a change set at once, and only the total effect Operation, but failing that ability, deferred constraints are the way SQLĪlso, having to enter records in a specific order, eg parent/child, is aĬontrivance given you're dealing with what should be a set-oriented database,Īnd a contrivance that can make using the database more difficult. Really need for this is the ability to change multiple tables as a single atomic Record may exist in table X if and only if a counterpart exists in table Y, suchĪs balancing records in a double-entry accounting system. They allow you to express constraints otherwise not possible, for example that a This only complicates things, I would rather see the statement fail immediately.ĭeferred constraints are definitely a benefit. Why does SQLite defer constraint violation errors? Is there a benefit? Would be a welcome addition, and hopefully be both easy enough to code, and Yuri's idea, with thus the FK name (and even which parent/child Willing to order their DMLs appropriately, a new pragma to fail on the Of course SQLite can't change its default behavior to "immediate" ![]() In parent-child order, and delete in child-parent order (unless using ONīut that's a good practice anyway IMHO, and does make troubleshooting on In Oracle for example, all FKs are validated immediately at the statementĪnd *can* be deferred, typically to deal with circular references. But that's in the case where FK constraints validation is deferred to Violated during a transaction, as long as the violation is resolved Remember, an FK constraint is allowed to be Therein lays the rub: there is no way to tell which (if any) FKĬonstraint has failed until you have run the operation to the end andĬhecked them all. In case of a 'FOREIGN KEYĬonstraint failed' I'm temporarily fine with a not so fast and memoryĮfficent sqlite, as long, as it helps me, to understand the situationĭon't keep the bag, keep only one integer ID of the first failed The way out may be ly in the words "at once". Memory and helpful constraint failed message. You made crystal clear, that we can't have both at once: fast / less Obviously, the name of the constraint would It's faster and need lesser memory.Īlthough, the plea stays. Malloc().) Removing an element from a bag takes more time thanĪ good explanation. (In particular,Īdding an element to a bag probably involves one or more calls to More, depending on how many elements it holds.) Adding an element toĪ bag takes more time than incrementing a counter. Of all the constraints that have been violated and then removeĮlements from the bag as constraints are resolved.Ī bag takes more run-time memory than a single counter. Necessary to have a bag (a list or hash table or an associative array) ![]() To provide information about which constraint(s) failed, it would be At the end, if theĬounter is greater than zero then a "foreign key constraint" error is ![]() The current foreign key constraint mechanism uses a single counter.Īs constraints are violated, the counter increments, and asĬonstraints are resolved the counter decrements. It is a substantial change (basically a complete rewrite of the entireįoreign key constraint mechanism) which would negatively impact both Of memorizing the ID of the constraint, and then printing its name in How hard is it to add this information to the message? Is this a matter This message always leaves the user wondering: "Which constraint?"
0 Comments
Leave a Reply. |