Check constraint for phone number format

Question

I keep getting an error:

[2020-01-27 10:37:04] [23000][4025] CONSTRAINT CONSTRAINT_1 failed

When I insert the following row:

'(01)236-4589'

For the following column:

CHECK (phone LIKE '(0[0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

Please tell me what I'm doing wrong.

Thank you very much for the help.

I suppose that you meant *phone number* format instead of *date* format in the title of your question. — GMB, Jan 26 '20 at 22:01
`CHECK` was not implemented until MariaDB 10.2 and MySQL 8.0.16. Fixes were added in subsequent releases. — Rick James, Jan 27 '20 at 01:11

score 0 · Accepted Answer · answered Jan 26 '20 at 21:57

0

Problems with your code:

to check a value against a regex, you need to use the REGEXP operator instead of LIKE
the opening and closing parenthese need to be escaped (this requires two backslashes)

Also, you can use quantifiers to avoid repeating [0-9] again and again.

Consider:

CHECK (phone REGEXP '\\(0[0-9]\\)[0-9]{3}-[0-9]{4}')

answered Jan 26 '20 at 21:57

GMB

Why would this be downvoted? @downvoter, please elaborate. – GMB Jan 27 '20 at 00:57

1 Answers1