On Mon, 6 Jul 2009 16:53:35 -0500, Nicolas Williams <nicolas.willi...@sun.com> wrote:
>On Sat, Jul 04, 2009 at 10:24:50AM +0200, Kees Nuyt wrote: >> On Fri, 03 Jul 2009 14:38:43 -0700, James Gregurich >> <bayouben...@mac.com> wrote: >> >> > >> >nuts. that makes INSERT OR REPLACE worthless if you have tables >> >dependent on one another. >> > >> > >> >Is there any way to manually get a list of records for which there >> >would be a conflict if a given record was inserted? >> >> BEGIN; >> INSERT the row, and observe any UNIQUE constraint failures. >> >> If there isn't one, the INSERT succeeded and you're done. > > Does SQLite3 report all those failuers in one attempt? It will probably only report one UNIQUE constraint failure at a time. If there is such a conflict, the use case determines what to do next. Typically there is just one UNIQUE key, the primary key, and that case is relatively simple. If there are more UNIQUE constraints, there has been a reason to make the primary key the primary one... And foreign keys usually point to the PRIMARY KEY of the referred table, not some other candidate key. > Or must one loop to make this a robust conflict > detection algorithm? What would you do, retry the INSERT with some other value for the offending key? Or UPDATE the row with that other value? As said, it all depends on the use case. I guess it has become more clear by now why the REFERENCES constraint is not implemented in SQLite, it really is a can of worms. -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
