MySQL require a minimum length

Admin 1
Viewed 1939

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`.
check-constraints mysql sql
5 Answers

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.

user_11440350

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;
user_12933200

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    |
+-----------+
user_15296730

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);
user_109082740

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
Bill_Karwin_208600
Related