The "OR NULL" doesn't work the way you think -- it's going to make the whole expression null, which apparently check constraints treat the same as not false. What you want there is "OR typeof(handedness)='null'".
Peter ----- Original Message ---- > From: Roman Fleysher <roman.fleys...@einstein.yu.edu> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Fri, May 17, 2013 12:19:21 PM > Subject: [sqlite] table with check > > Dear SQLiters, > > I am using sqlite shell, I believe version 3.7.16.2. I created a table with >CHECK condition as: > > CREATE TABLE subject( > subjectID INTEGER PRIMARY KEY, > handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL) > ); > > in hopes to be able to insert only "Right", "Left" or nothing "", i.e. fail >otherwise. But: > > INSERT INTO subject (subjectID,"qqq"); > > actually inserts qqq. Am I doing something wrong? I read manual that newer >versions of sqlite should enforce CHECKs. > > Thank you, > > Roman > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
