CODEWITHSUNDEEP
mysqlcheck-constraints
ptrn
ptrn

Reputation: 5202

Using a check contraint in MySQL for controlling string length

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', '[email protected]');

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.

Upvotes: 5

Views: 12985

Answers (3)

enharmonic
enharmonic

Reputation: 2080

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.")

Upvotes: 1

ilgaar
ilgaar

Reputation: 844

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 ;

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

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;

Upvotes: 6

Related Questions