On 2017/08/18 1:47 PM, Richard Hipp wrote:
On 8/18/17, Wout Mertens <wout.mert...@gmail.com> wrote:
So, bottom line, is there a way to insert or replace a row so that first
the id constraint is observed (replacing a previous row with the same id),
and then the k constraint is verified (failing to replace if k is already
present in the table)?
CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
CREATE INDEX demo_k ON demo(k);
CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
SELECT raise(ABORT,'uniqueness constraint failed on k')
FROM demo WHERE k=new.k;
END;
The above will force uniqueness on k for INSERT statements. You'll
want a second "BEFORE UPDATE" trigger to do similar enforcement for
UPDATEs if that is an issue for you.
Isn't this what conflict clauses on constraints are for?
Wouldn't the following achieve the same?:
CREATE TABLE demo(
id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
k TEXT UNIQUE ON CONFLICT ABORT,
otherstuff ANY
);
To amuse myself, I've tested it and it works as expected - New IDs that
conflict are replaced, but a conflicting k gets aborted - but am I
missing some fine-print or hidden caveat?
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
