Using a check contraint in MySQL for controlling string length

mysql check-constraints
ptrn picture ptrn · Jun 12, 2010 · Viewed 8.9k times · Source

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

OMG Ponies picture OMG Ponies · Jun 12, 2010

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;

Related questions

CHECK constraint in MySQL is not working

First I created a table like CREATE TABLE Customer ( SD integer CHECK (SD > 0), Last_Name varchar (30), First_Name varchar(30) ); and then inserted values in that table INSERT INTO Customer values ('-2','abc','zz'); MySQL doesn't show an error, …

Read More
How do I add a custom CHECK constraint on a MySQL table?

I am having trouble with this table CREATE TABLE `Participants` ( `meetid` int(11) NOT NULL, `pid` varchar(15) NOT NULL, `status` char(1) DEFAULT NULL, PRIMARY KEY (`meetid`,`pid`), CONSTRAINT `participants_ibfk_1` FOREIGN KEY (`meetid`) REFERENCES `Meetings` (`meetid`) ON DELETE CASCADE CONSTRAINT `participants_…

Read More
MySql workbench CHECK constraint

Here I want to create 2 CHECK constraint before the record insert to the database. ALTER TABLE SubjectEnrollment ADD CONSTRAINT register CHECK (register <= classSize AND register >=0), ADD CONSTRAINT available CHECK (available <= classSize AND available >= 0); register attribute should …

Read More