Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    52

    Question Unanswered: Check Constraint

    I have an existing table with field ZIPCODE defined as VARCHAR(5).
    I want to add a check constraint to allow only number from 0 to 9.

    This is what I did but it gave me error:

    alter table test
    with check
    add constraint ck_test
    check (zip between '0' and '9')

    error:

    ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
    The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.


    What did I do wrong. Thanks for your help.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't have access to bol and my machine...I'll figure it out tomorrow, unless you get it tonight

    BUT

    Look at LIKE [0-9][0-9][0-9][0-9][0-9]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    80

    Re: Check Constraint

    I guess u r trying to check a single digit as zip.
    existing data in the table conflicts with the constraint u r trying to add
    so try to alter table with nocheck option.
    or else drop the column and add it in which ever way u want.
    -----------------------------------------------------------------------------
    alter table test
    with nocheck
    add constraint ck_test
    check (zip between '0' and '9')
    -----------------------------------------------------------------------------


    Originally posted by ttkh
    I have an existing table with field ZIPCODE defined as VARCHAR(5).
    I want to add a check constraint to allow only number from 0 to 9.

    This is what I did but it gave me error:

    alter table test
    with check
    add constraint ck_test
    check (zip between '0' and '9')

    error:

    ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
    The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.


    What did I do wrong. Thanks for your help.

  4. #4
    Join Date
    Apr 2010
    Posts
    2

    RE:Check Constraint

    Check the Previous value inserted in the Column 'Zipcode'.The previous value must match the constraint defined. ie: the previously inserted value must be between 0 and 9

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dear madhavkarki, do you really think that after 6 years ttkh is still trying to debug this problem??

    holy "late to the party" batman
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    *gasp* *pant* Party time I'm herrrrrrrrre...*urghhhh*
    Quote Originally Posted by r937 View Post
    holy "late to the party" batman
    Aw poop

  7. #7
    Join Date
    Apr 2010
    Posts
    2

    check constraint

    oh! I've just created a new login and was searching for the threads .I didnt see the post date and replied instantly. Sorry friends!

Posting Permissions

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