Integer field size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Integer field size

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-02 : 12:25:09

I've got a field of type int, is there any way I can make it only accept values with 1 digit - i.e. 0 to 9

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-02 : 12:37:35

Why don't you put this validation logic in the front-end. That's the logical place for it rather than having it at database level.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-02 : 13:08:40

check yourColumn between 0 and 10

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-02 : 16:52:11

quote:
Originally posted by harsh_athalye

Why don't you put this validation logic in the front-end. That's the logical place for it rather than having it at database level.

While I agree that you should have a front-end check. If it is a business rule then it probably makes sense to have a check constraint on the database. Some businesses enforce their business rules with the database and some don't, depends on how you do it.

I personaly tend to put these constraints in my database since it would enforce the business rule and trying to get bad data into the database will fail if someone is able to circumvent the front-end.

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-10-02 : 17:05:33

quote:
Originally posted by spirit1

check yourColumn between 0 and 10

Where do I put this statement?

Also should it be

check yourColumn between 0 and 9? I want to allow 0,1,2,3,4,5,6,7,8,9

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-02 : 17:20:55

[code]
CREATE TABLE t1
(ID int PRIMARY KEY, somenum tinyint)
GO
ALTER TABLE t1 ADD CONSTRAINT CK_t1_0_to_9
CHECK (somenum >= 0 AND somenum <= 9)
[/code]

between translates to col >= firstValue and col < lastValue

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-10-03 : 07:37:13

quote:
Originally posted by spirit1
between translates to col >= firstValue and col < lastValue

I'm sure it is just a type-o, but ...

quote:
BOL
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

Jay
to here knows when

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 07:42:17

[code]DECLARE @Sample TABLE (i INT)

INSERT @Sample
SELECT 1 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 9

SELECT i
FROM @Sample
WHERE i BETWEEN 5 AND 7[/code]

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 09:23:08

yes it was a typo...

thanx!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources