Adding a constraint question

Can you add a constraint using the below syntax but to check whether a field
has only numeric digits?
So that if Threshold was updated to say 'marc1' it would fail.
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold CHECK (Threshold in ('Y','N'))Something like this?
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (isnumeric(Threshold) = 1)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"marcmc" wrote:

> Can you add a constraint using the below syntax but to check whether a fie
ld
> has only numeric digits?
> So that if Threshold was updated to say 'marc1' it would fail.
> ALTER TABLE misre_threshold WITH NOCHECK
> ADD CONSTRAINT Threshold CHECK (Threshold in ('Y','N'))|||Try this:
...check (<column name> not like '%[^0-9]%')
ML
http://milambda.blogspot.com/|||thanks guys BUT
I tried the following:
but the Threshold Column is dataType money
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (isnumeric(convert(money,Threshold)) =
1)
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (Threshold not like '%[^0-9]%')
Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.|||Aha, I assumed it was a character column (you haven't mentioned the actual
data type).
Use CAST in the constraint:
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (cast(Threshold as varchar(64)) not
like '%[^0-9]%')
ML
http://milambda.blogspot.com/|||By the way - have you checked out this nice article regarding the ISNUMERIC
function?
http://www.aspfaq.com/show.asp?id=2390
ML
http://milambda.blogspot.com/|||thanks that does build it but my .net application runs as follows and so
encounters an error, should i change my .net app? I would rather not.|||Thanks for that..
Kewl article.. But I am using 2000 SP4 and the results are different. But
some misses nevertheless.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"ML" wrote:

> By the way - have you checked out this nice article regarding the ISNUMERI
C
> function?
> http://www.aspfaq.com/show.asp?id=2390
>
> ML
> --
> http://milambda.blogspot.com/|||Whenever a constraint is violated the SQL Server returns an error saying so,
and the error should be handled by the calling application. Isn't the
constraint already implemented on the application tier? Why not?
What exactly do you need?
An alternative is to use a trigger and simply rollback any transactions that
would otherwise violate the constraint, but this is IMHO a kludge, since the
data would not be inserted and the user would not be notified of this - in m
y
view - vital consequence.
ML
http://milambda.blogspot.com/|||I see your point.
I don't know what your exposure is to validating data on a datagrid within
.net but it aint pretty.
sample ddl,
What I want is the first update to break the constraint, the second should
not.
create table marc(
ThresholdType varchar(6),
Threshold money)
insert into marc values('RecEst', 20000.00)
ALTER TABLE marc WITH NOCHECK
ADD CONSTRAINT Threshold CHECK (cast(Threshold as varchar(64)) not
like '%[^0-9]%')
select * from marc
update marc set Threshold = 'marc1' where ThresholdType = 'RecEst'
update marc set Threshold = 20001.00 where ThresholdType = 'RecEst'