Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following table.

create table test (
   id smallint unsigned AUTO_INCREMENT,
   age tinyint not null,
   primary key(id),
   check (age<20)
);

The problem is that the CHECK constraint does not work on the age column. For example, when I insert 222 for the age field MySQL accepts it.

share|improve this question

3 Answers 3

up vote 6 down vote accepted

What you need are two triggers to catch the invalid age condition

  • BEFORE INSERT
  • BEFORE UPDATE

The following is based on a jerry-rigged error trapping method for MySQL Triggers from Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers':

drop table mytable; 
create table mytable ( 
    id smallint unsigned AUTO_INCREMENT, 
    age tinyint not null, 
    primary key(id) 
); 
DELIMITER $$  
CREATE TRIGGER checkage_bi BEFORE INSERT ON mytable FOR EACH ROW  
BEGIN  
    DECLARE dummy,baddata INT;  
    SET baddata = 0;  
    IF NEW.age > 20 THEN  
        SET baddata = 1;  
    END IF;  
    IF NEW.age < 1 THEN  
        SET baddata = 1;  
    END IF;  
    IF baddata = 1 THEN  
        SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid')  
        INTO dummy FROM information_schema.tables;
    END IF;  
END; $$  
CREATE TRIGGER checkage_bu BEFORE UPDATE ON mytable FOR EACH ROW  
BEGIN  
    DECLARE dummy,baddata INT;  
    SET baddata = 0;  
    IF NEW.age > 20 THEN  
        SET baddata = 1;  
    END IF;  
    IF NEW.age < 1 THEN  
        SET baddata = 1;  
    END IF;  
    IF baddata = 1 THEN  
        SELECT CONCAT('Cannot Update This Because Age ',NEW.age,' is Invalid')  
        INTO dummy FROM information_schema.tables;
    END IF;  
END; $$  
DELIMITER ;  
insert into mytable (age) values (10);
insert into mytable (age) values (15);
insert into mytable (age) values (20);
insert into mytable (age) values (25);
insert into mytable (age) values (35);
select * from mytable;
insert into mytable (age) values (5);
select * from mytable;

Here is the result:

mysql> drop table mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable (
    ->     id smallint unsigned AUTO_INCREMENT,
    ->     age tinyint not null,
    ->     primary key(id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER checkage_bi BEFORE INSERT ON mytable FOR EACH ROW
    -> BEGIN
    ->     DECLARE dummy,baddata INT;
    ->     SET baddata = 0;
    ->     IF NEW.age > 20 THEN
    ->         SET baddata = 1;
    ->     END IF;
    ->     IF NEW.age < 1 THEN
    ->         SET baddata = 1;
    ->     END IF;
    ->     IF baddata = 1 THEN
    ->         SELECT CONCAT('Cannot Insert This Because Age ',NEW.age,' is Invalid')
    ->         INTO dummy FROM information_schema.tables;
    ->     END IF;
    -> END; $$
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TRIGGER checkage_bu BEFORE UPDATE ON mytable FOR EACH ROW
    -> BEGIN
    ->     DECLARE dummy,baddata INT;
    ->     SET baddata = 0;
    ->     IF NEW.age > 20 THEN
    ->         SET baddata = 1;
    ->     END IF;
    ->     IF NEW.age < 1 THEN
    ->         SET baddata = 1;
    ->     END IF;
    ->     IF baddata = 1 THEN
    ->         SELECT CONCAT('Cannot Update This Because Age ',NEW.age,' is Invalid')
    ->         INTO dummy FROM information_schema.tables;
    ->     END IF;
    -> END; $$
Query OK, 0 rows affected (0.07 sec)

mysql> DELIMITER ;
mysql> insert into mytable (age) values (10);
Query OK, 1 row affected (0.06 sec)

mysql> insert into mytable (age) values (15);
Query OK, 1 row affected (0.05 sec)

mysql> insert into mytable (age) values (20);
Query OK, 1 row affected (0.04 sec)

mysql> insert into mytable (age) values (25);
ERROR 1172 (42000): Result consisted of more than one row
mysql> insert into mytable (age) values (35);
ERROR 1172 (42000): Result consisted of more than one row
mysql> select * from mytable;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  2 |  15 |
|  3 |  20 |
+----+-----+
3 rows in set (0.00 sec)

mysql> insert into mytable (age) values (5);
Query OK, 1 row affected (0.07 sec)

mysql> select * from mytable;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  2 |  15 |
|  3 |  20 |
|  4 |   5 |
+----+-----+
4 rows in set (0.00 sec)

mysql>

Please also notice that auto increment values are not wasted or lost.

Give it a Try !!!

share|improve this answer
    
sorry i wanna ask to RolandoMySQL DBA,, in the syntax you make the default error to be ('Cannot Update This Because Age ',NEW.age,' is Invalid'),, but why when it running the error change to be error 1172,, it is mean the syntax does not return one row, is not it? –  user10659 Aug 5 '12 at 7:12
    
@astrinursetiawati The error message comes from the SELECT CONCAT(...) INTO dummy FROM information_schema.tables;. This query was deliberately put in place to cause the trigger to break right there. Unfortunately, that's the only way to break a MySQL trigger. I could have easily put SELECT 'Hello' FROM information_schema.tables WHERE badcolumn=1; (no such column called badcolumn) or SELECT 'Hello' FROM information_schema.table WHERE badcolumn=1; (no such table called information_schema.table). The idea is to get the trigger to halt. The error message is nonsequiter at that point. –  RolandoMySQLDBA Aug 6 '12 at 16:44

CHECK constraints are not implemented in MySQL. From CREATE TABLE

The CHECK clause is parsed but ignored by all storage engines. See Section 12.1.17, “CREATE TABLE Syntax”. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.8.5, “MySQL Differences from Standard SQL”.

It's also been a reported bug for almost 8 years...

share|improve this answer
2  
It's not a bug. It's a featureless feature. +1 !!! –  RolandoMySQLDBA Dec 23 '11 at 16:01
2  
Since it's ignored by design, I'd consider it a misfeature rather than a bug. –  Mike Sherrill 'Cat Recall' Dec 24 '11 at 14:36
    
Although @gbn points out that > CHECK constraints are not implemented in MySQL ... It's also been a reported bug for almost 8 years... it may be worth noting that MariaDb (the post-Oracle open-source fork of MySql by its original author) does –  CrazyPyro Feb 26 at 19:10
1  
No, MariaDB has not implemented CHECK constraints either. The page you link is talking about the SQL-99 standard, not MariaDB's implementation. Read "Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB." –  ypercube Feb 26 at 19:31

Besides the nice trigger solution by @Rolando, there's another workaround of this problem in MySQL (until CHECK constraints are implemented).

So, if you prefer referential integrity constraints and want to avoid triggers (because of the issues in MySQL when you have both in your tables), you can use another small reference table:

CREATE TABLE age_allowed
  ( age TINYINT UNSIGNED NOT NULL
  , PRIMARY KEY (age)
  ) ENGINE = InnoDB ;

Fill it with 20 rows:

INSERT INTO age_allowed
  (age)
VALUES
  (0), (1), (2), (3), ..., (19) ;

Then your table would be:

CREATE TABLE test 
  ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
  , age TINYINT UNSIGNED NOT NULL
  , PRIMARY KEY (id)
  , CONSTRAINT age_allowed__in__test 
      FOREIGN KEY (age)
        REFERENCES age_allowed (age)
  ) ENGINE = InnoDB ;

You'll have to remove write access to the age_allowed table, to avoid accidental adding or removing of rows.

This trick will not work with FLOAT datatype columns, unfortunately (too many values between 0.0 and 20.0).

share|improve this answer
    
Nice workaround –  a_horse_with_no_name Aug 5 '12 at 9:07
3  
(until CHECK constraints are implemented), see, that's what I call faith. –  Camilo Martin Jan 19 '13 at 11:06
    
OMG I just looked at this. That's a brilliant workaround for reasonably finite ranges. –  RolandoMySQLDBA Feb 26 at 21:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.