database column int limit

Question

How can I limit my database column's integral input to a specific number of digits ?

CREATE TABLE tab (id INT <1 digit> not null, value INT <10 digits> not null);

Thank you

score 6 · Accepted Answer · edited May 23 '17 at 12:16

6

Add a check constraint (SQL Server) or trigger (MySQL, doesn't support CHECK constraints)

SQL Server example:

CREATE TABLE tab (
    id tinynot null CHECK (id BETWEEN 0 AND 9), 
    value INT not null CHECK (id BETWEEN 1000000000 AND 9999999999)
);

If you only want one digit though, then use tinyint
If you aren't storing numbers (eg "123456789 bananas") but, say, phone numbers then use a varchar type. See https://stackoverflow.com/a/8284674/27535

Edit, you'd need a trigger in MySQL

edited May 23 '17 at 12:16

Community

1
1

answered Feb 20 '12 at 13:01

gbn

394,550
75
549
647

CHECK syntax not suitable as it's for MySQL (I realise this was clarified after this answer, however just posting up for clarity) – Simon at My School Portal Feb 20 '12 at 13:06
@Simonatmso.net: that was clarified after my answer – gbn Feb 20 '12 at 13:08

score 0 · Answer 2 · edited Feb 21 '12 at 12:20

0

The short version is using TINYINT UNSIGNED NOT NULL will be a more suitable data type, but it can't limit the values stored.

The longer version is that you may wish to read up on MySQL integer data types. You'll see that TINYINT is sufficient for your purpose as that is a 1-byte column that stores values from -128 to +127, or 0 to +255.

Secondly if you define it as TINYINT(1) then you are defining it as being a TINYINT with a display width of 1 digit. This will not prevent values larger than 10 being stored though. For more reading on this behaviour check numeric type attributes.

edited Feb 21 '12 at 12:20

Leigh

28,424
10
49
96

answered Feb 20 '12 at 13:00

Simon at My School Portal

6,630
19
41

Thank you, you know what I am doing ? I am going to have to do a real project tomorrow possibly on my own, don't anyone just ever "drop" me...Thanks. :-D – user1155788 Feb 20 '12 at 13:17
By the way, I think I will user VARCHAR(1). It's better as I don't need to read anymore anything. -:D – user1155788 Feb 20 '12 at 13:19
VARCHAR(1) uses 2 bytes of data, when CHAR(1) uses 1 byte due to VARCHAR needing 1 byte (at short lengths) to know how long the string is (VARCHAR = 1 byte per character in each individual string + 1 byte for length @ strings up to and including 255 characters long, while a char is 1 byte per character regardless of individual strings) therefore if you KNOW every cell will have a 1-byte string i.e. single character, then use CHAR(1) – Simon at My School Portal Feb 20 '12 at 13:20

database column int limit

2 Answers2