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
