On Fri, 13 May 2011 12:06:23 -0400, Jean-Christophe Deschamps
<[email protected]> wrote:
> Dear list,
>
> Is there a way to make SQLite accept this kind of constraint:
>
> CREATE TABLE tab (
> id INTEGER NOT NULL,
> data INTEGER,
> CHECK(data = 0 or not exists (select 1 from tab where id = data)));
Off the top of my head, I do not believe subqueries are allowed at all in
CHECK constraints. Hmmm…
sqlite> CREATE TABLE "One" ("id" INTEGER);
sqlite> CREATE TABLE "Two" ("other" INTEGER,
...> CHECK ("other" IN (SELECT "id" FROM "One")));
Error: subqueries prohibited in CHECK constraints
sqlite>
It appears I remembered correctly. Yes, I also once had a use case for
that; I solved it easily with a trigger. Not sure about the doc issue you
also mention.
Very truly,
SAMUEL ADAM ◊ http://certifound.com/ ◊ I read list mail sporadically.
763 Montgomery Road ◊ Hillsborough, NJ 08844-1304 ◊ United States
April 15, 2011 Courtroom Video in re Adam v. Supreme Court of N.J.:
http://www.youtube.com/watch?v=GPw2W2-Ujyc
>
> This toy exemple doesn't mean much as it is and the actual situation is
> a bit more involved. Anyway, the crux of it is that the table name
> doesn't yet exist when the parser looks at the constraint, hence SQLite
> issues a "no such table: tab" error.
>
> The docs say that a check table constraint can be any expression but
> this is clearly not the complete picture.
>
> I tend to think that this statement should be accepted, but I'm in no
> way an expert in ISO SQL.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
