CODEWITHSUNDEEP
mysql
Greg Gomez
Greg Gomez

Reputation: 60

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?

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:

00001
010
100
1000

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

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

Thanks! Greg

Upvotes: 0

Views: 298

Answers (4)

spencer7593
spencer7593

Reputation: 108490

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.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

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;

Upvotes: 1

Mike
Mike

Reputation: 2761

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.

Upvotes: 1

branx
branx

Reputation: 43

Make the column type int(5) maybe?

Upvotes: 0

Related Questions