On Sat, 21 Jan 2017 18:14:06 +0000
Simon Slavin <slav...@bigfraud.org> wrote:
> It is a failure of atomicity in SQLite semantics.
>
> This is one I do feel is a bug in SQLite.
Thank you for your support. I feel it's important to understand it's a
bug, not a feature.
> I think I?ve seen other SQL implementations where you can state at
> which point the constraints are enforced.
Every other SQL DBMS I'm aware of handles things like primary key
constraint and unique constraint correctly out of the box. Any
constraint that affects only one table can be enforced atomically, i.e.
after each SQL statement.
You need deferred constraint enforcement for things that can't be
expressed atomically in SQL. For example, assume two tables,
orders and order_items, with two rules:
1. every order must have at least one order_item
2. every order_item must belong to an order
In creating a new order, these requirements are impossible to fill
simultateously, because INSERT affects only one table. A workaround
like a permanent faux_item introduces needless compexity. Deferred
constraint enforcement can apply contraints after both inserts.
In Tutorial-D, Date uses a comma-operator to chain database updates
together, to indicate that the combination is atomic.
IMO deferred constraints are way outside the scope of SQLite. It's
complex. Simple type enforcement and correct constraint enforcement
would serve users better.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
