[sqlite] INSERT OR IGNORE safety?

[Ivan Shmakov]

        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