November 26, 2010 at 2:07 pm
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://qa.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
November 26, 2010 at 5:03 pm
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
November 26, 2010 at 7:28 pm
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
November 27, 2010 at 3:26 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply