I wonder if I'm on safe side when I use, say:
INSERT OR IGNORE INTO "foo" ("foo")
VALUES (?1);
INSERT INTO "bar" ("foo")
SELECT f."rowid"
FROM "foo" f
WHERE f."foo" = ?1;
(within a transaction) against the following schema:
CREATE TABLE "foo" ("foo" TEXT NOT NULL);
CREATE INDEX "foo-unique" ON "foo" ("foo");
CREATE TABLE "bar" ("foo" INTEGER NOT NULL REFERENCES "foo");
I mean, if the first INSERT fails for the reason other than the
UNIQUE constraint set, the second one will fail too, and it's
the success of the second one that I was interested in the first
place.
JFTR, the other possible ways to implement the first INSERT that
I'm aware of, are:
INSERT INTO "foo" ("foo")
SELECT ?1
WHERE NOT EXISTS (SELECT 1 FROM "foo" f WHERE f."foo" = ?1);
INSERT INTO "foo" ("foo")
SELECT ?1
EXCEPT SELECT f."foo" FROM "foo" f;
--
FSF associate member #7257
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
