Check Constraint for Nullable Phone Numbers Column - MySQL

Question

Constraint to check on MySQL server-side if a phone number consists of only numbers or not and whether it has ten digits or not.

I have the following create table query where mobile is the name of the field, storing the phone numbers of people: -

CREATE TABLE `users` (
  --Other columns

  `mobile` int UNSIGNED DEFAULT NULL, 

) ENGINE=InnoDB;

As indicated, the mobile column can be NULL. Hence, I can't apply direct length check constraints and other regex constraints. What would be a feasible check constraint, if any?

P.S. - I have assumed the phone numbers to be an INTEGER. I have read suggestions for making it varchar.

EDIT - (Take care of the version)

Prior to version 8.0, MySQL didn't enforce the check constraints. It allowed them to be written in CREATE TABLE statements, parsed them and ignored them.

Here's some useful documentation from MySQL Reference Manual:

Prior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored:

CHECK (expr)

Phone numbers are not integers, as they are not "countables", they're not numbers you do math with, sum, average, or treat them as numbers. They're sequences of digits, which is a string. There are plenty of countries and areas that use region numbers, country numbers, area codes, whatever, that start with a 0, and you're going to have some oddities if you treat it as an integer. — Lasse V. Karlsen, Jan 31 '20 at 10:06
So what is the logic you want to implement in your constraint? Ensure that the phone number is either null or, has 10 digits? — GMB, Jan 31 '20 at 10:06
`0123456789` is a valid phone number. If saved as integer, it becomes `123456789` — Cid, Jan 31 '20 at 10:07
Can't you use something like `CONSTRAINT mobile_constraint CHECK (mobile IS NULL OR LEN(mobile) = 10 OR ...)` ? — Lasse V. Karlsen, Jan 31 '20 at 10:08
@GMB Yes, exactly that. And I have also changed the phone number field to varchar on popular demand. Hence, I want to perform a regex check too ensuring that it contains only digits. — Akash Das, Jan 31 '20 at 10:08
also useful: https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working — Sebastian Brosch, Jan 31 '20 at 10:14

score 1 · Accepted Answer · answered Jan 31 '20 at 10:11

It has been vastly commented already that a phone number should be stored as string instead of an integer (a typical example being phone numbers that start with 0).

You could use the following check constraint:

check(mobile is null or mobile regexp '^[0-9]{10}$')

This ensures that mobile is either null or contains exactly 10 digits.

If you want to ensure that the phone number contains only digits but not necessarily 10 digits, then you can change the quantifier from 10 to +:

check(mobile is null or mobile regexp '^[0-9]+$')

Sadly, the check constraint does not work. It accepts numbers that have less than 10 digits and even strings with letters in place of digits. — Akash Das, Jan 31 '20 at 12:58

Check Constraint for Nullable Phone Numbers Column - MySQL

1 Answers1