On Fri, 13 May 2011 12:06:23 -0400, Jean-Christophe Deschamps <j...@antichoc.net> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users