Professional Web Applications Themes

Null in check - Microsoft SQL / MS SQL Server

Hi, if I insert the NULL value in CHECK constraint with IN the SQL generated don't take in consideration the NULL value. For example: ALTER TABLE MyTable ADD CONSTRAINT CkMyTable_CODE CHECK(CODE IN(NULL, 'A','B','C')) In Enterprise Manager / Table / Check the sql generated by the check don't test the NULL with IS but with the equal. The result is that I can insert EVERY value in my CODE column. This is a very malicious bug and in my opinion there are two solutions: 1) accept the null in CHECK(IN ... clause but the sql generated must take in consideration the ...

  1. #1

    Default Null in check

    Hi, if I insert the NULL value in CHECK constraint with IN the SQL generated
    don't take in consideration the NULL value.

    For example:
    ALTER TABLE MyTable ADD CONSTRAINT CkMyTable_CODE CHECK(CODE IN(NULL,
    'A','B','C'))

    In Enterprise Manager / Table / Check the sql generated by the check don't
    test the NULL with IS but with the equal.
    The result is that I can insert EVERY value in my CODE column.

    This is a very malicious bug and in my opinion there are two solutions:
    1) accept the null in CHECK(IN ... clause but the sql generated must take in
    consideration the NULL condition with IS NULL
    2) don't accept the NULL with an error code

    Thank. Paolo


    Paolo Guest

  2. #2

    Default Re: Null in check

    I'm not sure I understand you. Could you explain why are you testing field value for NULLs in CHECK CONSTRAINT.

    Define NULL (or NOT NULL) in column definition.

    If you must define "NULL-ity" of the column in check constraint then use IS NULL or IS NOT NULL

    Your check constraint as you defined it: ALTER TABLE MyTable ADD CONSTRAINT CkMyTable_CODE CHECK(CODE IN(NULL,'A','B','C')) will
    return UNKNOW when you try to insert NULL value. You obviously have CODE column defined so that it allows null and you don't get any
    errors.

    I tried to find out how constraints behieve when logical expression evaluates to UNKNOWN but didn't find the explanation. Does any
    one know.

    You should use this:

    ALTER TABLE MyTable ADD CONSTRAINT CkMyTable_CODE CHECK((CODE is NULL) or CODE in ('A','B','C'))

    or better (actually the same as previous):

    create table MyTable
    (ID int identity(1,1) primary key
    , code char(1) NULL
    )

    ALTER TABLE MyTable ADD CONSTRAINT CkMyTable_CODE CHECK(CODE in ('A','B','C'))
    --
    Dean Savovic
    www.teched.hr


    "Paolo" <Com> wrote in message news:Oi7q$phx.gbl... 


    Dean Guest

  3. #3

    Default Re: Null in check

    > The result is that I can insert EVERY value in my CODE column.
    uh?
    IF NULL IN (NULL, 'B')
    SELECT 'Null'
    Doesn't return a result with me, and
    IF 'A' IN (NULL, 'B')
    SELECT 'Null'
    Doesn't return a value either.
    You can insert every value in the list except NULL.
     
    Nope sorry, it's because NULLs work differently than you think. A NULL isn't
    equal to anything, not even to another NULL. That's why you have to check
    with IS NULL if the variable or column contains a NULL. If you change your
    check to :
    ALTER TABLE MyTable ADD CONSTRAINT CkMyTable_CODE CHECK(CODE IN('A','B','C')
    OR CODE IS NULL)
    it will work as you require.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Paolo" <Com> wrote in message
    news:Oi7q$phx.gbl... 
    generated 
    in 


    Jacco Guest

  4. #4

    Default Re: Null in check

    Paolo,

    a CHECK constraint will make sure no invalid data enters the column, so
    it will disallow rows where the expression of the check constraint is
    FALSE.

    Let's have a look at your example.

    CHECK( CODE IN (NULL, 'A'))

    is the same as

    CHECK( CODE = NULL OR CODE = 'A')

    let's say CODE = 'B'. Then the evalulation will be

    CHECK( UNKNOWN OR FALSE )

    which evaluates to UNKNOWN. Since the result is not FALSE, the row will
    be allowed.

    All the above is standard ANSI-SQL.

    BTW: the easiest way to disallow NULLs from a column is to simply change
    the column definition to NOT NULL.

    Hope this helps,
    Gert-Jan


    Paolo wrote: 
    Gert-Jan Guest

  5. #5

    Default Re: Null in check

    Paolo,

    Nothing is wrong here. A CHECK constraint prevents
    the insertion of rows for which the constraint evaluates to
    FALSE. The expression CODE IN (NULL, 'A','B','C')
    cannot be FALSE because of the NULL, so the constraint
    will prevent no insertion.

    It isn't the way you might expect it to work, but it does
    follow rules, and as others have suggested, you can constrain
    the column as needed. Note that NULL can be inserted even
    with CODE IN ('A') as a constraint, also:

    create table T (
    check_null int check (check_null in (NULL)),
    check_4 int check (check_4 = 4)

    )
    go

    insert into T values (1, null)
    go

    select * from T
    go

    drop table T

    Steve Kass
    Drew University


    Paolo wrote:
     

    Steve Guest

  6. #6

    Default Re: Null in check

    Of course int works this way!

    In DDL, the CHECK() fails when the test is FALSE; the TRUE and UNKNOWN
    results pass. In DML, the WHERE and ON clauses fail when the test is
    FALSE or UNKNOWN and pass when the results are TRUE. You need to read
    an intro book on SQL-92.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Check for null query
    By T McDizzle in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 6th, 04:50 PM
  2. how to to check array Null value on coldfusion 7
    By jiecoldfusion in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: February 28th, 02:36 PM
  3. Question: How check if datareader is null?
    By John Saunders in forum ASP.NET General
    Replies: 3
    Last Post: August 12th, 01:02 PM
  4. Check for Null In Conditional Formating
    By Allen Browne in forum Microsoft Access
    Replies: 1
    Last Post: August 1st, 01:21 PM
  5. Error: ?null? is null or not an object
    By Murray *TMM* in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 10th, 06:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139