CODEWITHSUNDEEP
mysqlsql
user1155788
user1155788

Reputation: 51

database column int limit

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

Upvotes: 1

Views: 6494

Answers (2)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

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.

Upvotes: 0

gbn
gbn

Reputation: 432210

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)
);

Edit, you'd need a trigger in MySQL

Upvotes: 6

Related Questions