Indeed.
CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK(x<y AND x IS NOT NULL AND y IS NOT NULL)
);
Works in pgsql.
What bugs me about this, though, is that if my check of (x<y) doesn't fail
(5, NULL), a "SELECT * FROM ex1 WHERE x<y" on that table should also return
that row, which it doesn't. After all, "x<y" is the same as "x<y". Grr.
Well now that I know, it's okay. Just a pitfall and NULL as always just
needing extra attention. Seems like a nightmare to implement though. The
check constraint can be ignored if one or more operands in a comparison is
NULL unless an explicit NOT NULL condition is found for that particular
operand? Err, or something.
-----Original Message-----
From: Andrew Piskorski [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 8:12 PM
To: [email protected]
Subject: Re: [sqlite] CHECK constraints
*snip*
At least in Oracle, no, your example insert works fine. If you want
the insert to fail, you need to add a "not null" constraint as well.
Nulls are always allowed unless you have a not null constraint.
*snip*
--
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/
