On Fri, Jan 21, 2011 at 10:21:02AM +0530, Sunil Bhardwaj scratched on the wall:
> How can we put check constraints on a column in sqlite, while
> creating a table.
A CHECK constraint can be defined as a column constraint or as a table
constraint. In SQLite, all CHECK constraints act as table-level
constrains, in that all types of CHECK constraints can reference any
column in the row. Both columns and tables allow more than one
check constraint. On any INSERT or UPDATE, all the constraints
are evaluated. If any of them evaluate to zero (false), a violation
is thrown.
At the column level:
CREATE TABLE (
...,
colName colType ... CHECK ( expr ) ...,
...
);
And at the table level:
CREATE TABLE (
...,
colName colType ...,
CHECK( expr ),
...
);
> We want to restrict values for a column in a range say '1 to 10'.
Lots of ways:
CREATE TABLE tbl (
i INTEGER CHECK ( i BETWEEN 1 AND 10 )
);
CREATE TABLE tbl (
i INTEGER CHECK ( i >= 1 AND i <= 10 )
);
CREATE TABLE tbl (
i INTEGER CHECK ( i >= 1 ) CHECK ( i <= 10 )
);
CREATE TABLE tbl (
i INTEGER,
CHECK ( i BETWEEN 1 AND 10 )
);
CREATE TABLE tbl (
i INTEGER,
CHECK ( i >= 1 AND i <= 10 )
);
CREATE TABLE tbl (
i INTEGER,
CHECK ( i >= 1 ),
CHECK ( i <= 10 )
);
In all cases, you should get this:
INSERT INTO tbl ( i ) VALUES ( 0 ) => Constraint violation
INSERT INTO tbl ( i ) VALUES ( 1 ) => OK
INSERT INTO tbl ( i ) VALUES ( 10 ) => OK
INSERT INTO tbl ( i ) VALUES ( 11 ) => Constraint violation
INSERT INTO tbl ( i ) VALUES ( NULL ) => OK
Note that last one... If the check constraint evals to NULL, the
operation is still allowed.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
