CHECK constraints

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

CHECK constraints

D. Richard Hipp
In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?

Example:

  CREATE TABLE ex1(
    x INTEGER,
    y REAL,
    CHECK( x<y )
  );

Then you do:

  INSERT INTO ex1 VALUES(5, NULL);

Does the check constraint fail or not?  Or do different
database engines do different things?

I need to know so that I can make check constraint in SQLite
work like they do in other database engines...
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: CHECK constraints

Marcus Welz
PostgreSQL 8.0 will happily insert (5, NULL).


-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: Wednesday, November 02, 2005 6:31 PM
To: [hidden email]
Subject: [sqlite] CHECK constraints

In a CHECK constraint, if the expression is NULL (neither true
nor false) does the constraint fail?

Example:

  CREATE TABLE ex1(
    x INTEGER,
    y REAL,
    CHECK( x<y )
  );

Then you do:

  INSERT INTO ex1 VALUES(5, NULL);

Does the check constraint fail or not?  Or do different
database engines do different things?

I need to know so that I can make check constraint in SQLite
work like they do in other database engines...
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: CHECK constraints

D. Richard Hipp
In reply to this post by D. Richard Hipp
"Marcus Welz" <[hidden email]> wrote:
> PostgreSQL 8.0 will happily insert (5, NULL).
>

Hmmm..  Not what I expected, nor what I implemented.
But the implementation is easily changed and there is
no point in trying to be "logical" about the behavior
of NULLs, I've learned.  I will probably modify SQLite
to conform to PostgreSQL unless there is a serious
outcry against this, or unless someone learns that
PostgreSQL intends to change their behavior...

I'm eager to hear what other RDBMSes do.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: CHECK constraints

Preston Zaugg
In reply to this post by D. Richard Hipp
MS SQL 2000 wil alsol insert (5, NULL)



>From: "Marcus Welz" <[hidden email]>
>Reply-To: [hidden email]
>To: <[hidden email]>
>Subject: RE: [sqlite] CHECK constraints
>Date: Wed, 2 Nov 2005 19:24:09 -0500
>
>PostgreSQL 8.0 will happily insert (5, NULL).
>
>
>-----Original Message-----
>From: [hidden email] [mailto:[hidden email]]
>Sent: Wednesday, November 02, 2005 6:31 PM
>To: [hidden email]
>Subject: [sqlite] CHECK constraints
>
>In a CHECK constraint, if the expression is NULL (neither true
>nor false) does the constraint fail?
>
>Example:
>
>   CREATE TABLE ex1(
>     x INTEGER,
>     y REAL,
>     CHECK( x<y )
>   );
>
>Then you do:
>
>   INSERT INTO ex1 VALUES(5, NULL);
>
>Does the check constraint fail or not?  Or do different
>database engines do different things?
>
>I need to know so that I can make check constraint in SQLite
>work like they do in other database engines...
>--
>D. Richard Hipp <[hidden email]>
>


Reply | Threaded
Open this post in threaded view
|

RE: CHECK constraints

Marcus Welz
In reply to this post by D. Richard Hipp
To be honest, I didn't expect that either. I guess with NULL meaning
"absence of a value" the logic here is that since it cannot determine the
value of NULL, it let's it pass. Why that was chosen over failing the check
doesn't make sense to me.

MySQL 3.23.58 gives a syntax error on the table definition.

MySQL 4.0.24 inserts the record fine -- but it also inserts (5, 4). i.e. it
seems to ignore the check altogether.


-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: Wednesday, November 02, 2005 7:43 PM
To: [hidden email]
Subject: Re: [sqlite] CHECK constraints

"Marcus Welz" <[hidden email]> wrote:
> PostgreSQL 8.0 will happily insert (5, NULL).
>

Hmmm..  Not what I expected, nor what I implemented.
But the implementation is easily changed and there is
no point in trying to be "logical" about the behavior
of NULLs, I've learned.  I will probably modify SQLite
to conform to PostgreSQL unless there is a serious
outcry against this, or unless someone learns that
PostgreSQL intends to change their behavior...

I'm eager to hear what other RDBMSes do.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: CHECK constraints

Darren Duncan
In reply to this post by D. Richard Hipp
I'm not sure if this applies, but in my experience it is normal for a
unique value constraint to be satisfied on columns with null values,
as is a foreign key constraint, which is only evaluated on not-null
values.  Following that precedent, I would say that the CHECK
constraint should pass if its expression results to null.

If you're trying to enforce a certain kind of behaviour in a
particular check constraint, you probably want to add some IS NULL
expressions to explicitly declare the behaviour you want, to specify
times when a null input would result in a check failure.

-- Darren Duncan

At 6:30 PM -0500 11/2/05, [hidden email] wrote:

>In a CHECK constraint, if the expression is NULL (neither true
>nor false) does the constraint fail?
>
>Example:
>
>   CREATE TABLE ex1(
>     x INTEGER,
>     y REAL,
>     CHECK( x<y )
>   );
>
>Then you do:
>
>   INSERT INTO ex1 VALUES(5, NULL);
>
>Does the check constraint fail or not?  Or do different
>database engines do different things?
>
>I need to know so that I can make check constraint in SQLite
>work like they do in other database engines...

Reply | Threaded
Open this post in threaded view
|

Re: CHECK constraints

Andrew Piskorski
In reply to this post by D. Richard Hipp
On Wed, Nov 02, 2005 at 06:30:51PM -0500, [hidden email] wrote:
> In a CHECK constraint, if the expression is NULL (neither true
> nor false) does the constraint fail?

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.

I'm not certain, but I believe this is standard behavior in all SQL
RDBMSs that support constraints, not just Oracle.

--
Andrew Piskorski <[hidden email]>
http://www.piskorski.com/
Reply | Threaded
Open this post in threaded view
|

RE: CHECK constraints

rbundy
In reply to this post by D. Richard Hipp

My information is that MySQL does not enforce check constraints - your
testing supports this.

Regards.

rayB



|---------+---------------------------->
|         |           "Marcus Welz"    |
|         |           <[hidden email]|
|         |           om>              |
|         |                            |
|         |           03/11/2005 11:59 |
|         |           Please respond to|
|         |           sqlite-users     |
|         |                            |
|---------+---------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       <[hidden email]>                                                                    |
  |       cc:                                                                                                    |
  |       Subject:  RE: [sqlite] CHECK constraints                                                               |
  >--------------------------------------------------------------------------------------------------------------|




To be honest, I didn't expect that either. I guess with NULL meaning
"absence of a value" the logic here is that since it cannot determine the
value of NULL, it let's it pass. Why that was chosen over failing the check
doesn't make sense to me.

MySQL 3.23.58 gives a syntax error on the table definition.

MySQL 4.0.24 inserts the record fine -- but it also inserts (5, 4). i.e. it
seems to ignore the check altogether.


-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: Wednesday, November 02, 2005 7:43 PM
To: [hidden email]
Subject: Re: [sqlite] CHECK constraints

"Marcus Welz" <[hidden email]> wrote:
> PostgreSQL 8.0 will happily insert (5, NULL).
>

Hmmm..  Not what I expected, nor what I implemented.
But the implementation is easily changed and there is
no point in trying to be "logical" about the behavior
of NULLs, I've learned.  I will probably modify SQLite
to conform to PostgreSQL unless there is a serious
outcry against this, or unless someone learns that
PostgreSQL intends to change their behavior...

I'm eager to hear what other RDBMSes do.

--
D. Richard Hipp <[hidden email]>








************** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING *************
******************* Confidentiality and Privilege Notice *******************

This e-mail is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone,
and you should destroy this message and kindly notify the sender by reply
e-mail. Confidentiality and legal privilege are not waived or lost by reason
of mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

****************************************************************************

Reply | Threaded
Open this post in threaded view
|

RE: CHECK constraints

Marcus Welz
In reply to this post by Andrew Piskorski
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:[hidden email]]
Sent: Wednesday, November 02, 2005 8:12 PM
To: [hidden email]
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 <[hidden email]>
http://www.piskorski.com/

Reply | Threaded
Open this post in threaded view
|

Re: CHECK constraints

Nemanja Corlija
In reply to this post by D. Richard Hipp
> Does the check constraint fail or not?  Or do different
> database engines do different things?
In Firebird 1.5 it does fail.

Though I agree with Darren's remarks, so not failing it seems to be
more flexible.

--
Nemanja Corlija <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: CHECK constraints

rlodina
Hi,

In DB2 7.x the insert statement is also ok.



On 11/3/05, Nemanja Corlija <[hidden email]> wrote:

>
> > Does the check constraint fail or not? Or do different
> > database engines do different things?
> In Firebird 1.5 it does fail.
>
> Though I agree with Darren's remarks, so not failing it seems to be
> more flexible.
>
> --
> Nemanja Corlija <[hidden email]>
>
Reply | Threaded
Open this post in threaded view
|

RE: CHECK constraints

Drew, Stephen
In reply to this post by D. Richard Hipp
Oracle 9.2

  CREATE TABLE ex1(
    x INTEGER,
    y NUMBER(21,18),
    CHECK( x<y )
  );

INSERT succeeds.

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: 02 November 2005 23:31
To: [hidden email]
Subject: [sqlite] CHECK constraints

In a CHECK constraint, if the expression is NULL (neither true nor
false) does the constraint fail?

Example:

  CREATE TABLE ex1(
    x INTEGER,
    y REAL,
    CHECK( x<y )
  );

Then you do:

  INSERT INTO ex1 VALUES(5, NULL);

Does the check constraint fail or not?  Or do different database engines
do different things?

I need to know so that I can make check constraint in SQLite work like
they do in other database engines...
--
D. Richard Hipp <[hidden email]>



Reply | Threaded
Open this post in threaded view
|

Re: CHECK constraints

Brad-19
In reply to this post by Preston Zaugg
> MS SQL 2000 wil alsol insert (5, NULL)

Unless you additionally constrain the fields with "not null", but that's
a violation the "not null" constraint, not the "x < y" constraint.