how can i put a check constraint to the phone number in mysql?

Question

to check the phone number that it should be of the 10 digits , so how can i put the check constraint so that it will be validated

i try using the simple technique but i guess it will check only the digit 10

Answer 1

instead of:

count(phone int, check(count(phone =10)))

try using a VACHAR type so you can use the length function to check if the value consists of 10 characters, as int types allow for negative values, which isn't what you want. You can implement a solution like this:

create table d (phone VARCHAR, check(length(phone) = 10))

to ensure the phone contains a value of length, 10. If your phone number column does not contain spaces, you could improve validation by using regex to strictly enforce a rule like this to ensure individual row entries contain digits and are not less or more than 10:

check(phone REGEXP '^[0-9]{10}$')