1

I want to add a check constraint on the primary key of the accomodatie table named code. This code has to start with 4 letters and end with 4 numbers (Abcd0123). I thought I could use a regexp for that in a check constraint like below:

alter table accomodatie add constraint check (
accomodatie.code regexp '/^[A-Z][a-z]{3}\d{4}$/' 
);

But when I want to execute the statement above I get an error:

Error Code: 3819. Check constraint 'accomodatie_chk_1' is violated.

I have also tried like instead of regexp but that does not do the trick.

Ronny Giezen
  • 557
  • 2
  • 10
  • 21

1 Answers1

2

Okay, I tested further and there are a few issues.

Don't use /<regexp>/. Using the slashes is borrowed from PHP or Perl, but MySQL doesn't use that. Get rid of the slashes.

The \d sequence is not supported. That's also borrowed from PHP or some other language. Use [[:digit:]] as a sequence that matches digits. Or good old [0-9].

mysql> select 'Abcd0123' regexp '^[A-Z][a-z]{3}[[:digit:]]{4}$' as regexp_match;
+--------------+
| regexp_match |
+--------------+
|            1 |
+--------------+

Also keep in mind the default collation in your session is probably case-insensitive, so there's no difference between [A-Z] and [a-z].

mysql> select 'abcd0123' regexp '^[A-Z][a-z]{3}[[:digit:]]{4}$' as regexp_match;
+--------------+
| regexp_match |
+--------------+
|            1 |
+--------------+

But you can use a case-sensitive collation if you want case mismatches to result in a regexp non-match:

mysql> select 'abcd0123' regexp '^[A-Z][a-z]{3}[[:digit:]]{4}$' collate utf8mb3_bin as regexp_match;
+--------------+
| regexp_match |
+--------------+
|            0 |
+--------------+

Review the documentation for MySQL's regular expression syntax: https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-syntax

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828