Using a check contraint in MySQL for controlling string length

Question

I'm tumbled with a problem!

I've set up my first check constraint using MySQL, but unfortunately I'm having a problem. When inserting a row that should fail the test, the row is inserted anyway.

The structure:

CREATE TABLE user (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  uname VARCHAR(10) NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  mail VARCHAR(50) NOT NULL,
  PRIMARY KEY (id),
  CHECK (LENGTH(fname) > 30)
);

The insert statement:

INSERT INTO user VALUES (null, 'user', 'Fname', 'Lname', 'mail@me.now');

The length of the string in the fname column should be too short, but it's inserted anyway.

I'm pretty sure I'm missing something basic here.

OMG Ponies · Accepted Answer · 2012-04-24T00:58:21.600

6

MySQL doesn't enforce CHECK constraints, on any engine.
Which leads me to ask, why would you declare the fname column as VARCHAR(50), but want to enforce that it can only be 30 characters long?

That said, the only alternative is to use a trigger:

CREATE TRIGGER t1 BEFORE INSERT ON user
 FOR EACH ROW BEGIN

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

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

END;

edited Apr 24 '12 at 00:58

answered Jun 12 '10 at 01:28

OMG Ponies

325,700
82
523
502

I want it to be at least 30 characters long (shorter than that really, I just put in 30 so that this scenario would fail), so any longer strings than that would be accepted – ptrn Jun 12 '10 at 01:30
4

From MySQL 8.0.16, `check` constraints are respected - https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html – youcantryreachingme Mar 16 '21 at 04:45

score 0 · Answer 2 · answered Jun 02 '18 at 04:01

As mentioned above you have to use a trigger, MySQL doesn't support check, also when you have multiple statements inside your trigger block, like declaring variables or control flows, you need to start it with begin and end and enclose your trigger inside two delimiters:

Note: If you use MariaDB use // after the first delimiter and before the second delimiter, otherwise if you use MySQL use $$ instead.

delimiter //

create trigger `user_insert_trigger` before insert on `user` for each row
begin
    declare maximumFnameLength int unsigned;
    declare fNameLength int unsigned;
    set maximumFnameLength = 30;
    set fNameLength = (select length(new.fNameLength));
    if (fNameLength > maximumFnameLength) then
        signal sqlstate '45000'
            set message_text = 'First name is more than 30 characters long.';
    end if;
end

//

delimiter ;

enharmonic · Answer 3 · 2023-05-23T23:10:58.487

As of Mysql 8.0.16, check constraints can be enforced:

CREATE TABLE socials (ssn varchar(9) NOT NULL, 
UNIQUE KEY ssn (ssn), 
CONSTRAINT socials_chk_1 CHECK ((length(ssn) = 9));

When you attempt to insert data of incorrect length, it fails:

insert into socials values('12345678'); -- too short
(3819, "Check constraint 'socials_chk_1' is violated.")

When you attempt to insert data of the correct length, it succeeds:

insert into socials values('123456789'); -- just right
Query OK, 1 row affected

But notice how the constraint has unexpected behavior when the varchar length matches the constraint length, and the input value is longer than expected:

insert into socials values('1234567800000'); -- too long
Query OK, 1 row affected
select * from socials;
+-----------+
| ssn       |
|-----------|
| 123456789 |
| 123456780 |
+-----------+

What happened? It looks like MySQL is truncating the input string to the varchar length and then checking the constraint, which results in "123456780" being inserted, which is not the desired behavior. As a workaround, make the varchar field at least one character longer than the constraint length, and the check will validate as expected:

alter table socials change ssn ssn varchar(10);

and then try inserting data longer than the check length:

insert into socials values('1234567800000'); -- too long
(3819, "Check constraint 'socials_chk_1' is violated.")

Using a check contraint in MySQL for controlling string length

3 Answers3