Limit record inserted into mysql to be exact 9 numbers

Question

I have a field in my database that is a phone number i want to make it only accept exact 9 numbers

Mysql version: 5.7.31

how about saving it as varchar(9) and validating on your app only numbers are allowed? — dariogriffo, Aug 04 '20 at 10:15

Akina · Accepted Answer · 2020-08-04T16:01:11.907

2

As column constraint:

CREATE TABLE my_table (
    ...
    phone_number INT 
                 NOT NULL 
                 CHECK (phone_number BETWEEN 100000000 AND 999999999),
    ... );

or as table constraint:

CREATE TABLE my_table (
    ...
    phone_number INT 
                 NOT NULL,
    CONSTRAINT phone_9_digits 
        CHECK (phone_number BETWEEN 100000000 AND 999999999),
    ... );

The constraint deals with one column only - so column constraint type is preferred.

How can i do this to a existing table?

Alter column definition or table definition. For example,

ALTER TABLE my_table
CHANGE COLUMN phone_number 
              phone_number INT 
                           NOT NULL 
                           CHECK (phone_number BETWEEN 100000000 AND 999999999);

If you use old MySQL version which does not support CHECK constraints then use triggers pack.

CREATE TRIGGER tr_bi_check_phone_for_9digits
BEFORE INSERT -- and the same for BEFORE UPDATE
ON my_table
FOR EACH ROW
BEGIN
    IF NEW.phone_number NOT BETWEEN 100000000 AND 999999999 THEN                        
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Phone Number must have 9 digits strictly.';
    END IF;
END

If phone_number is stored as a string then checking condition phone_number BETWEEN 100000000 AND 999999999 may be replaced, for example, with

phone_number REGEXP '[0-9]{9}'

This will also allow you to store phone numbers that start from zero.

edited Aug 04 '20 at 16:01

answered Aug 04 '20 at 10:17

Akina

39,301
5
14
25

How can i do this to a existing table? – mohammad hayajneh Aug 04 '20 at 10:18
@mohammadhayajneh By according ALTER TABLE ADD CONSTRAINT. – Akina Aug 04 '20 at 10:18
Can you add example on alter table too please. – mohammad hayajneh Aug 04 '20 at 10:23
CHANGE COLUMN mobile mobile INT NOT NULL CHECK (mobile BETWEEN 100000000 AND 999999999); [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHECK (mobile BETWEEN 100000000 AND 999999999)' at line 5 – mohammad hayajneh Aug 04 '20 at 10:25
@mohammadhayajneh Maybe your MySQL version is too ancient? you have not specified it... if your version does not support CHECK constraint then use BEFORE INSERT and BEFORE UPDATE triggers with according checking and SIGNAL if the check fails. – Akina Aug 04 '20 at 10:26
this is my verison 5.7.31-0ubuntu0.16.04.1 @Akina – mohammad hayajneh Aug 04 '20 at 10:32
@mohammadhayajneh The solution for your version added. All solutions assumes that phone number is stored in a column of INT datatype. – Akina Aug 04 '20 at 10:33
I wouldn't store the field as an integer - it won't allow you to store internation dialing codes (which usually start with a zero), or local phone numbers for many other countries (which also start with a zero). – Neville Kuyt Aug 04 '20 at 10:50
Don't you also need to check that only digits are entered? – Rick James Aug 04 '20 at 15:45
@NevilleKuyt The variant for string datatype value added. – Akina Aug 04 '20 at 16:03
@RickJames All variants does not allow to store non-digit chars (but INTEGER datatype allows to enter a value with excess leading zeros, decimal point and even fractional part which will be truncated). – Akina Aug 04 '20 at 16:05
Validating phone number formats is pretty complex. See https://stackoverflow.com/questions/123559/how-to-validate-phone-numbers-using-regex – Neville Kuyt Aug 04 '20 at 16:20
@NevilleKuyt There is no the task to validate the phone number "in general". – Akina Aug 04 '20 at 16:35

score 1 · Answer 2 · answered Aug 04 '20 at 10:17

Firstly, I would not want to implement this logic in the database. It's likely to change (not every country has the same phone number layout, and even within a country, sometimes the phone number format changes). You may want to impose some kind of formatting (e.g. separating area code from number). It's generally not great to use database errors as a way of communicating formatting issues - the user interface would need to understand that logic, and it's not particularly easily managed.

But if you really want to do it in the databse, you can create a trigger to validate the input before writing it to the table, and throw an error otherwise.

score 0 · Answer 3 · answered Aug 04 '20 at 18:33

Have you ever filled out a form, only to have it spit at you for not using the exact spacing, dashes, parens, etc that it expected for postal code, phone, credit card number?

For the sake of the users, I strongly recommend you allow any commonly allowed spacing, etc. Then remove extra stuff. If anything goes wrong, spit at the user form the UI, not the database. Data in the database should be cleansed by the UI.

What about the +1 (or whatever country code) that goes in front of international-savvy phone numbers? Or is your app a single country app, and will always be such?

Limit record inserted into mysql to be exact 9 numbers

3 Answers3