Creating a constraint for a phone or zip code in MYSQL?

Question

Creating the following table (MySQL 8.0):

create table Customer_Dim(
customer_ID     int not null,
FirstName       varchar(30) not null,
LastName        varchar(30) not null,
Gender          char(1) not null CHECK (gender in ('m','f')),
Street1         varchar(100) not null,
Street2         varchar(100) null,
City            varchar(30) not null,
StateAbbrev     char(2) not null,
ZipCode         char(5) not null CHECK (ZipCode LIKE repeat('[0-9]',5)),
PrimaryPhone    varchar(10) not null CHECK (PrimaryPhone LIKE repeat('[0-9]',10)),
EmailAddress    varchar(50) default null,
constraint custkey Primary Key(customer_ID)
);

but when I enter the following insert statement:

insert into Customer_DIM
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '80002', '2023459767', '[email protected]')

It fails the check on phone and zipCode. From what I could read it should work?

Answer 1

I believe that a regexp is needed over repeat (which repeats chars)

Using repeat would expect something like this:

mysql> select repeat('[0-9]', 5);

+---------------------------+
| repeat('[0-9]', 5)        |
+---------------------------+
| [0-9][0-9][0-9][0-9][0-9] |
+---------------------------+

So, use a regexp instead:

create table Customer_Dim(
customer_ID     int not null,
FirstName       varchar(30) not null,
LastName        varchar(30) not null,
Gender          char(1) not null CHECK (gender in ('m','f')),
Street1         varchar(100) not null,
Street2         varchar(100) null,
City            varchar(30) not null,
StateAbbrev     char(2) not null,
ZipCode         char(5) not null CHECK (ZipCode regexp '[0-9]{5}'),
PrimaryPhone    varchar(10) not null CHECK (PrimaryPhone regexp'[0-9]{10}'),
EmailAddress    varchar(50) default null,
constraint custkey Primary Key(customer_ID)
);

Same (good) example insert:

insert into Customer_Dim
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '80002', '2023459767', '[email protected]')

Bad phone example insert:

insert into Customer_Dim
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '80002', '202359767', '[email protected]')

Gives the error:

Check constraint 'Customer_Dim_chk_3' is violated.

Bad zip example insert:

insert into Customer_Dim
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '8002', '2023459767', '[email protected]')

Gives the error:

Check constraint 'Customer_Dim_chk_2' is violated.

DB fiddle example with all of the above info. (including the repeat example last)

Know that the MySQL check constraints were parsed but ignored before version 8.0.16: