How to enforce storing a number with a specific format (must begin with any single integer except 0 and be exactly a specific length) in MySQL?

Question

Edit: As I've been reading and responding to the excellent replies, it's occurred to me that my original question was incomplete. I've updated the Title to be more specific, as well as the question itself. I hope this is an acceptable way of reflecting the evolution of my thinking about this question. As I say, the responses have been really great and have guided me to a more accurate statement of the problem.

Here's the restated question: Is it possible to enforce storing a number with a specific format in MySQL? The format is: the number must begin with any integer except 0 and is exactly 5 digits long. As a regular expression, it would look like this: /[1-9]\d\d\d\d/.

Valid examples:

10001
20002
33333

Some invalid examples:

The field will be used for joins if that makes any difference.

Is there a way to enforce this 'type' in MySQL?

Thanks! Greg

`CHAR(5)` plus INSERT/UPDATE triggers or application level verification. — bishop, Feb 24 '17 at 22:04
@bishop: This is what I was thinking as well. In the case you suggest, CHAR(5), however, the field still accepts data like 1, 12, 123, etc. Is that just The Way It Is? Thanks! — Greg Gomez, Feb 24 '17 at 23:21
@Strawberry Oops 10000 is valid. I'm invalid when I say that it's not valid. I'll fixify it now. Thanks!!! — Greg Gomez, Feb 25 '17 at 01:08
Possible duplicate of [What is the benefit of zerofill in MySQL?](http://stackoverflow.com/questions/5256469/what-is-the-benefit-of-zerofill-in-mysql) — Merlin, Feb 25 '17 at 01:51
Great, so we now know that valid values are numbers in the range 10000 to 99999. — Strawberry, Feb 25 '17 at 07:12
@Strawberry Yes. If the number is treated like an INT or DEC, that's the range. In reality, it's a sort of serial number and the first digit must be [1-9]. — Greg Gomez, Feb 28 '17 at 17:24

Mike · Answer 1 · 2017-02-25T00:51:48.403

1

Within the database, the maximum number of digits is specified eg Decimal(5,2) would be decimal with 5 digits, 2 of which are after the decimal point.

However, because they are stored as integers it is up to the application to insert leading zeroes. The only way to restrict a range (eg >= 10,000) would be to add a user function to validate on insert and update.

Edit Actually, Mysql has an option for this. Declaring the column as:

intTest decimal(5,0) UNSIGNED ZEROFILL

will automatically pad it to your required 5 digits.

edited Feb 25 '17 at 00:51

answered Feb 24 '17 at 22:02

Mike

2,721
1
15
20

There are no cases where there are less than 5 digits. In your suggestion, the column would accept 1, 10, etc. So, I need something that accepts only 5 digits, no more no less, if possible. Thanks! – Greg Gomez Feb 24 '17 at 23:18
Oops, I ignored the part about user functions. Or client logic, too. Sorry about that. Also DECIMAL(5) works. No need for the second argument, decimal precision. – Greg Gomez Feb 24 '17 at 23:24
1

The decimal precision part was to give a complete answer - (5,0) or simply (5) would give you 5 digits only. – Mike Feb 24 '17 at 23:57
1

Or construct the query itself in such a way so as to handle the verification – Strawberry Feb 25 '17 at 00:25
1

@Mike In that case the column will accept 1 and turn it into 00001, no? If so, that's not the use case. It's gotta be 5 digits or die. – Greg Gomez Feb 25 '17 at 01:10
1

Yes, that's exactly what it does. Otherwise you need to set up a trigger to test the range on write and update test it in the code. – Mike Feb 25 '17 at 01:32
@Mike Oops sorry, I meant it's gotta be 5 digits beginning with [1-9]. – Greg Gomez Feb 28 '17 at 17:26
In that case, all you need is a constraint that the number is between 10000 and 99999. No fancy coding needed. – Mike Mar 01 '17 at 20:53

score 1 · Answer 2 · answered Feb 25 '17 at 07:30

E.g.

Note that this example allows duplicate values. A UNIQUE constraint and an INSERT IGNORE (or ON DUPLICATE KEY) revision would fix that...

Create table my_table 
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,i INT NOT NULL );

Insert into my_table (i) SELECT 50000 WHERE 50000 BETWEEN 10000 and 99999;

spencer7593 · Accepted Answer · 2017-02-27T17:06:31.537

There's no declarative way to restrict the values stored in a column to the domain that was specified: "five digit numbers".

We take that to mean that this will be stored as a numeric, and there won't be decimal point. That is, the specification is to allow only positive five digit integers, in the range 10000 to 99999.

To have the database enforce this type restriction, we would need to implement BEFORE INSERT and BEFORE UPDATE triggers.

As an example, we could raise a generic SQLSTATE as an error, whenever an attempt to insert or update a value that is out of range.

Assuming mycol is defined as integer type:

DELIMITER $$

CREATE TRIGGER mytable_bi
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
  IF NOT ( NEW.mycol >= 10000 AND NEW.mycol <= 99999 ) THEN
     SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'value out of range 10000 to 99999'
          , COLUMN_NAME = 'mycol'
          , TABLE_NAME = 'mytable'
     ;
  END IF; 
END$$

A similar BEFORE UPDATE trigger would also be required.

In earlier versions of MySQL don't support SIGNAL, we can emulate it by causing an error to be thrown. (Throwing an error will prevent the firing INSERT or UPDATE operation from completing successfully.) In place of SIGNAL, we can execute a SQL statement that is guaranteed to throw an error.

Another alternative is to use a foreign key constraint against a "domain table".

Create a table with a single column (same datatype as mycol) and populate it with the set of all possible valid values, and omit all invalid values.

 CREATE TABLE five_digit_integers (i INT NOT NULL PRIMARY KEY );
 INSERT INTO five_digit_integers VALUES (10000),(10001),(10002),...;
 ...
 INSERT INTO five_digit_integers VALUES ...,(99998),(99999);

Then, create a foreign key constraint:

 ALTER TABLE mytable
   ADD CONSTRAINT FK_mytable_mycol_five_digit_integer
   FOREIGN KEY (mycol) REFERENCES five_digit_integer (i)
 ;

Attempts to insert or update "out of range" values in mytable.mycol will result in a foreign key constraint error, and prevent the INSERT or UPDATE from completing successfully.

Of course, this doesn't prevent someone from inserting additional values into the five_digit_integers table, or from getting around the constraint by issuing a SET FOREIGN_KEY_CHECKS=0.

As per my example, that slightly depends upon your definition of 'declarative'. — Strawberry, Feb 28 '17 at 17:34
@Strawberry Yup. I've restated my original question in no small part due to your input. Thanks! — Greg Gomez, Feb 28 '17 at 18:03
Marking this one as correct because it's complete with good examples. The discussion here has been excellent, and everybody who participated had good things to offer. — Greg Gomez, Feb 28 '17 at 23:35

score 0 · Answer 4 · answered Feb 24 '17 at 22:01

0

Make the column type int(5) maybe?

answered Feb 24 '17 at 22:01

branx

43
5

2

In MySQL, the length specifier on INTEGER type does not restrict the values that can be stored. Yes, the column could be defined as INT(5), but this wouldn't achieve the objectives OP specified. – spencer7593 Feb 24 '17 at 22:11
No. This is a common misconception about how INTs work in MySQL. There are lots of questions and answers about it.Been there done that. – Greg Gomez Feb 24 '17 at 23:14

How to enforce storing a number with a specific format (must begin with any single integer except 0 and be exactly a specific length) in MySQL?

4 Answers4