MySQL require a minimum length

Question

I'm using MySQL Workbench and I made a table called 'organizations' and want to block any try of adding a value to a column with less than 5 letters. The column name is 'namee'. I made this, but I get an error:

ALTER TABLE organizations
ADD CONSTRAINT MINIMO CHECK (LENGTH(namee) >= 5);

Error:

Error Code: 3814. An expression of a check constraint 'MINIMO' contains disallowed function: `LEN`.

Answer 1

Based on the error message you shared, you apparently tried to use a function LEN(). No built-in function of that name exists in MySQL.

Testing with MySQL 8.0.21, I can reproduce the error you showed if I try using LEN() or any other nonexistent function.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE organizations ADD CONSTRAINT MINIMO CHECK (LEN(namee) >= 5);
ERROR 3814 (HY000): An expression of a check constraint 'MINIMO' contains disallowed function: `LEN`.

mysql> ALTER TABLE organizations ADD CONSTRAINT MINIMO CHECK (BOGUS(namee) >= 5);
ERROR 3814 (HY000): An expression of a check constraint 'MINIMO' contains disallowed function: `BOGUS`.

If you had tried to define a stored function called LEN() and use that, you should read https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html:

Stored functions and user-defined functions are not permitted.

But LENGTH() works without error. By the way, I'd recommend to use CHAR_LENGTH() so multibyte characters are counted as one character.

mysql> ALTER TABLE organizations ADD CONSTRAINT MINIMO CHECK (CHAR_LENGTH(namee) >= 5);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

Answer 2

That seems odd. Does this work?

ALTER TABLE organizations
    ADD CONSTRAINT MINIMO CHECK (LENGTH(namee) LIKE '_____');

I suspect that LENGTH() is non-deterministic; I am not sure why this would be.