problems with the check in a sql table

Question

i've created this table in SQL

CREATE TABLE product (
    code CHAR(7) NOT NULL,  
    name VARCHAR(30) NOT NULL, 
    Description VARCHAR(500) NOT NULL,
    cost DOUBLE UNSIGNED NOT NULL,  
    PRIMARY KEY (code),
    check(substring(code,1,3) like '%[a-z]%'
      and substring(code,4,4) like '%[0-9]%'), 
);

the value 'code' must consist of 3 characters and 4 numbers, but it doesn't work. what's wrong in the check?

Although `CHECK` is accepted when creating a table in MySQL it's not enforced until MySQL 8.0.13 (I may be wrong the the exact version of it, but it should be around that one). Which version of MySQL are you using? — The Impaler, Jun 12 '20 at 16:36
There's a comma in excess at the end. Please post the exact error message. "Doesn't work" is not appropriate. — The Impaler, Jun 12 '20 at 16:38

score 0 · Answer 1 · answered Jun 12 '20 at 16:39

the value 'code' must consist of 3 characters and 4 numbers, but it doesn't work. what's wrong in the check?

Use regular expressions:

check (code regexp '^[A-Z]{3}[0-9]{4}$')

MySQL does not extend the definition of LIKE to include character classes. It has real regular expression support.

problems with the check in a sql table

1 Answers1