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

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', 'test@aol.com')

Bad phone example insert:

insert into Customer_Dim
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '80002', '202359767', 'test@aol.com')

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', 'test@aol.com')

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: