This is my column
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
Asked
Active
Viewed 897 times
0
-
You could do that in your application code, or your query – Strawberry May 14 '16 at 12:20
-
That should do the trick. How does the result differ from what you expected? – Mureinik May 14 '16 at 12:20
-
@Mureinik The number in parentheses has no bearing on the range of values which the data type will accept. – Strawberry May 14 '16 at 12:24
-
the type limits just the maximum length. if you want to store explicitly 4 digits only, you must do validation on front-end or server side level before any insert into database. the other way is to add leading/trailing zeros (which is useless more or less). – mitkosoft May 14 '16 at 12:25
-
...or include the validation within the query itself. – Strawberry May 14 '16 at 12:25
-
Thanks I did both validation (php validation and jquery) but I thought I can do it in mysql too – M.A.O.2 May 14 '16 at 12:31
-
How Can I validate in query @Strawberry – M.A.O.2 May 14 '16 at 12:32
2 Answers
1
"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 |
+------+
Strawberry
- 33,750
- 13
- 40
- 57
0
From MySQL manual:
MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits.
The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.
So, you cannot limit only 4 digit values in mysql this way. And MySQL does not supports custom CHECK constraints. But you can create trigger like this and check value inside. Note, that SIGNAL works since MySQL 5.5.
