Arash Sahel Arash Sahel - 6 months ago 71
SQL Question

Small Int Type Length

This is my column
enter image description here

As It's length is specified (smallint(4)), but it does not constrain the range of values
, so how can I constrain that only 4 digit values can be entered in this column

Answer

"In query" validation can be done like this:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (year INT NOT NULL);

INSERT INTO my_table SELECT 2016 FROM (SELECT 1) n WHERE 2016 BETWEEN 0 AND 9999;
Query OK, 1 row affected (0.00 sec)

SELECT * FROM my_table;
+------+
| year |
+------+
| 2016 |
+------+

INSERT INTO my_table SELECT 20161 FROM (SELECT 1) n WHERE 20161 BETWEEN 0 AND 9999;
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM my_table;
+------+
| year |
+------+
| 2016 |
+------+