On Wed, Nov 18, 2015 at 4:29 AM, Richard Hipp <drh at sqlite.org> wrote:
> On 11/17/15, Yuri <yuri at rawbw.com> wrote: > > Don't keep the bag, keep only one integer ID of the first failed > > constraint. > > Therein lays the rub: there is no way to tell which (if any) FK > constraint has failed until you have run the operation to the end and > checked them all. Remember, an FK constraint is allowed to be > violated during a transaction, as long as the violation is resolved > before the end of the transaction. > True. But that's in the case where FK constraints validation is deferred to the transaction end. In Oracle for example, all FKs are validated immediately at the statement level by default, and *can* be deferred, typically to deal with circular references. It does force you to insert/update in parent-child order, and delete in child-parent order (unless using ON DELETE CASCADE), but that's a good practice anyway IMHO, and does make troubleshooting on errors so much easier. Of course SQLite can't change its default behavior to "immediate" validation, but for clients willing to order their DMLs appropriately, a new pragma to fail on the first validation error using Yuri's idea, with thus the FK name (and even which parent/child tables/columns involved perhaps) would be a welcome addition, and hopefully be both easy enough to code, and cheap enough to not incur any slowdown. "Le beurre et l'argent du beurre" [1] quoi. --DD [1] https://en.wiktionary.org/wiki/le_beurre_et_l%27argent_du_beurre
