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.
What you need are two triggers to catch the invalid age condition
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 !!!
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...
Besides the nice trigger solution by @Rolando, there's another workaround of this problem in MySQL (until CHECK constraints are implemented).
How to emulate some CHECK constraints in MySQL
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).
How to emulate arbitrary CHECK constraints in MySQL (5.7) and MariaDB (from 5.2 up to 10.1)
Since MariaDB added computed columns in their 5.2 version (GA release: 2010-11-10) and MySQL in 5.7 (GA release: 2015-10-21) - which they call them VIRTUAL and GENERATED respectively - that can be persisted, i.e. stored in the table - they call them PERSISTENT and STORED respectively - we can use them to simplify the above solution and even better, extend it to emulate/enforce arbitrary CHECK constraints):
As above, we will need a help table but with a single row this time that will be acting as an "anchor" table. Even better, this table can be used for any number of CHECK constraints.
We then add a computed column that evaluates to either TRUE / FALSE / UNKNOWN, exactly as a CHECK constraint would - but this column has a FOREIGN KEY constraint to our anchor table. If the condition/column evaluates to FALSE for some rows, the rows are rejected, due to the FK.
If the condition/column evaluates to TRUE or UNKNOWN (NULL), the rows are not rejected, exactly as it should happen with CHECK constraints:
CREATE TABLE truth
( t BIT NOT NULL,
PRIMARY KEY (t)
) ENGINE = InnoDB ;
-- Put a single row:
INSERT INTO truth (t)
VALUES (TRUE) ;
-- Then your table would be:
-- (notice the change to `FLOAT`, to prove that we don't need)
-- (to restrict the solution to a small type)
CREATE TABLE test
( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
age FLOAT NOT NULL,
age_is_allowed BIT -- GENERATED ALWAYS
AS (age >= 0 AND age < 20) -- our CHECK constraint
STORED,
PRIMARY KEY (id),
CONSTRAINT check_age_must_be_non_negative_and_less_than_20
FOREIGN KEY (age_is_allowed)
REFERENCES truth (t)
) ENGINE = InnoDB ;
The example is for MySQL 5.7 version. In MariaDB (versions 5.2+ up to 10.1), we just need to modify the syntax and declare the column as PERSISTENT instead of STORED. In version 10.2 the STORED keyword was added as well, so the example above works in both flavours (MySQL and MariaDB) for the latest versions.
If we want to enforce many CHECK constraints (which is common in many designs), we just have to add a computed column and a foreign key for each one of them. We only need one truth table in the database. It should have one row inserted and then all write access removed.
In the latest MariaDB however, we don't have to perform all these acrobatics any more, as CHECK constraints have been implemented in version 10.2.1 (alpha release : 2016-Jul-04)!
The current 10.2.2 version is still a beta version but it seems that the feature will be available in the first stable release of the MariaDB 10.2 series.
As I explained in this article, starting with version 8.0.16, MySQL has added support for custom CHECK constraints:
ALTER TABLE topic
ADD CONSTRAINT post_content_check
CHECK (
CASE
WHEN DTYPE = 'Post'
THEN
CASE
WHEN content IS NOT NULL
THEN 1
ELSE 0
END
ELSE 1
END = 1
);
ALTER TABLE topic
ADD CONSTRAINT announcement_validUntil_check
CHECK (
CASE
WHEN DTYPE = 'Announcement'
THEN
CASE
WHEN validUntil IS NOT NULL
THEN 1
ELSE 0
END
ELSE 1
END = 1
);
Previously, this was only available using BEFORE INSERT and BEFORE UPDATE triggers:
CREATE
TRIGGER post_content_check BEFORE INSERT
ON topic
FOR EACH ROW
BEGIN
IF NEW.DTYPE = 'Post'
THEN
IF NEW.content IS NULL
THEN
signal sqlstate '45000'
set message_text = 'Post content cannot be NULL';
END IF;
END IF;
END;
CREATE
TRIGGER post_content_update_check BEFORE UPDATE
ON topic
FOR EACH ROW
BEGIN
IF NEW.DTYPE = 'Post'
THEN
IF NEW.content IS NULL
THEN
signal sqlstate '45000'
set message_text = 'Post content cannot be NULL';
END IF;
END IF;
END;
CREATE
TRIGGER announcement_validUntil_check BEFORE INSERT
ON topic
FOR EACH ROW
BEGIN
IF NEW.DTYPE = 'Announcement'
THEN
IF NEW.validUntil IS NULL
THEN
signal sqlstate '45000'
set message_text = 'Announcement validUntil cannot be NULL';
END IF;
END IF;
END;
CREATE
TRIGGER announcement_validUntil_update_check BEFORE UPDATE
ON topic
FOR EACH ROW
BEGIN
IF NEW.DTYPE = 'Announcement'
THEN
IF NEW.validUntil IS NULL
THEN
signal sqlstate '45000'
set message_text = 'Announcement validUntil cannot be NULL';
END IF;
END IF;
END;
For more details about emulating CHECK constraints using database triggers for MySQL versions prior to 8.0.16, then check out this article.
