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', '[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.

Answer 1

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 length INT;
   SET num = (SELECT LENGTH(NEW.fname));

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

END;