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`.

You can build the logic into the insert itself – Strawberry Sep 03 '20 at 15:59 — Strawberry, Sep 03 '20 at 15:59

Gordon Linoff · Answer 1 · 2020-09-03T15:59:21.490

1

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.

edited Sep 03 '20 at 15:59

answered Sep 03 '20 at 15:52

Gordon Linoff

1,242,037
58
646
786

score 1 · Accepted Answer · answered Sep 03 '20 at 18:38

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

score 0 · Answer 3 · answered Sep 03 '20 at 15:56

At least in older versions, and you didn't specify which version, a trigger is needed for checking length.

CREATE TRIGGER t1 BEFORE INSERT ON organizations
 FOR EACH ROW BEGIN

   DECLARE numLength INT;
   SET numLength = (SELECT LENGTH(NEW. namee));

   IF (numLength > 30) THEN
     SET NEW.col = 1/0;
   END IF;

END;

score 0 · Answer 4 · answered Sep 03 '20 at 16:13

Consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (my_string VARCHAR(20) NOT NULL);

SET @string = 'red';

INSERT INTO my_table SELECT @string FROM (SELECT 1)x WHERE CHAR_LENGTH(@string) >= 5;

SET @string = 'orange';

INSERT INTO my_table SELECT @string FROM (SELECT 1)x WHERE CHAR_LENGTH(@string) >= 5;

SELECT * FROM my_table;
+-----------+
| my_string |
+-----------+
| orange    |
+-----------+

score 0 · Answer 5 · answered Sep 03 '20 at 16:58

0

I am not sure which version you are using but it works fine for me on MYSQL 8.x

create table test_check(name varchar(10));

ALTER TABLE test_check
ADD CONSTRAINT MINIMO CHECK (LENGTH(name) >= 5);

answered Sep 03 '20 at 16:58

Atif

2,011
9
23

MySQL require a minimum length

5 Answers5