Constraint for non-integer values

  • Hi, I have a table with an integer column, and I need to write a check constraint on that column that would only allow integer values and error out if a non-integer value was inserted.

    For example it should error out on 2.1 and not on 2

    I was trying to use http://www.sqlservercentral.com/scripts/T-SQL+Aids/30897/ but could not get it to work when applied as a constraint Any help would be appreciated.

    Thanks

  • Would have to check it before insertion...is the value being passed as a variable anywhere?

    If you do:

    Create table concheck (

    myint int not null

    constraint isInt check (Ceiling((cast(myint as numeric(23,1))/1000)*1000) =myint)

    )

    go

    /*Fail--record is inserted*/

    Insert into concheck

    Select 1.1

    The constraint doesn't hold.

    But you make sure the variable of the proc accepts decimal places, you can check using the same logic

    Drop table concheck

    go

    Create table concheck (myint int not null)

    go

    Create proc pInsertInt (@myint numeric(23,1))

    as

    If Ceiling((@myint/1000)*1000) != cast(@myint as int)

    Begin

    Print @myint

    Print 'Could not be inserted'

    END

    If Ceiling((@myint/1000)*1000) = cast(@myint as int)

    Begin

    Insert into concheck (myint)

    Select @myint

    End

    go

    Exec pInsertInt 1

    Go

    Exec pInsertInt 1.1

    Go

    drop table concheck

    drop proc pInsertInt

    Not elegant or masterful, but would fail anything passed with a decimal place value

  • The following works. The only bugger about it is that you insert into one column and select from the other...

    CREATE TABLE #MyHead

    (

    SomeValue VARCHAR(11) CONSTRAINT ck_IntOnly CHECK (SomeValue NOT LIKE '%[^0-9]%'),

    SomeInt AS CAST(SomeValue AS INT) PERSISTED

    );

    GO

    --===== This is no longer allowed and will produce a check constraing violation message.

    INSERT INTO #MyHead (SomeValue)

    SELECT 2.1

    GO

    --===== Even the following isn't allowed.

    INSERT INTO #MyHead (SomeValue)

    SELECT 2.0

    GO

    --===== But this is

    INSERT INTO #MyHead (SomeValue)

    SELECT 2.

    GO

    --===== And so is this

    INSERT INTO #MyHead (SomeValue)

    SELECT 2

    GO

    Of course, the best thing to do would be to validate the data before you try to insert it into the table so you don't actually need two columns to pull this off. Still, even a human can't get by it without disabling the constraint.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • CELKO (11/26/2010)


    If this is an INTEGER column as you said, then that is all you can insert into it.

    True enough. But I believe the problem is that you can get an INSERT of 2.1 to pass without an error even though it becomes an INTEGER through implicit converson. I believe that the OP is trying to make it so that the column will not accept any non-whole number.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply