CODEWITHSUNDEEP
mysqlcheck-constraints
Ronny Giezen
Ronny Giezen

Reputation: 645

Use rexexp in check constraint Mysql

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.

Upvotes: 1

Views: 469

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

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

Upvotes: 2

Related Questions