MySQL server check constraint with range

Question

I'm trying to understand how to properly use the check constraint, but it keeps on failing when i use ranges;

mysql> create table test( code varchar(64), constraint ctr check (code like '[0-9]%')) :
Query OK, 0 rows affected (0.00 sec) 
-- Allow strings that starts with a number, but when i try to insert '5foo' for example:

mysql> insert into test (code) values ('5foo' ) ;
ERROR 3819 (HY000) : Check constraint 'ctr' is violated.

Never the less using a normal check constraint works just fine, for example :

mysql> create table test( code varchar(64), constraint ctr check(code like '_foo_bar%'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test (code) values ('5foo0barabc');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (code) values ('55foo0barabc');
ERROR 3819 (HY000): Check constraint 'test_chk_1' is violated.

as you can see.

I would like to understand why it's not working, and if ranges just aren't a thing in mySQL (because i read that the check constraint it self wasn't part of it earlier before, im using Ver 8.0.28 for macos11 on arm64 (MySQL Community Server - GPL) btw if it's of any cause) is there a way to achieve the following check (which is what im actually trying to achieve, the upper code is just an example): ([A-Z][0-9]{9}) a string that starts with an uppercase letter and is followed by 9 numbers.

Thank you guys in advance!

Bill Karwin · Accepted Answer · 2022-04-18 22:31:12Z

2

MySQL (and standard SQL) does not support character ranges in the LIKE predicate. The only pattern metacharacters supported by LIKE are % and _ (and I suppose \ to escape the other metacharacters).

Microsoft SQL Server implements character ranges for LIKE, but this is their own custom extension to standard SQL.

For MySQL, you need to use REGEXP instead of LIKE. But then % is not a pattern-matching character. Use the regular expression equivalent .* instead, or else just leave that part out, because regular expressions implicitly allow any characters to follow your pattern. Also, for a regular expression you need to use ^ to make pattern match the start of the string, otherwise it will match if it matches any substring within.

Example:

create table test( code varchar(64), constraint ctr check (code regexp '^[0-9].*'))

Alternative:

create table test( code varchar(64), constraint ctr check (code regexp '^[0-9]'))

answered Apr 18, 2022 at 22:31

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

telos Over a year ago

Thank you !, '^([A-Z][0-9]{9})' achieves what i want with a char(10).

Collectives™ on Stack Overflow

MySQL server check constraint with range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related