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

Question

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_ibfk_2` CHECK (status IN ('a','d','u'))
  CONSTRAINT `participants_ibfk_3` CHECK (pid IN (SELECT name FROM Rooms) OR pid IN (SELECT userid FROM People))
);

I want to have a foreign key constraint, and that works. Then, I also want to add a constraint to the attribute status so it can only take the values 'a', 'd' and 'u'. It is not possible for me to set the field as Enum or set.

Can anyone tell me why this code does not work in MySQL?

As others mentioned before, check constaints were **finally** introduced with MySQL 8.0.16: https://mysqlserverteam.com/mysql-8-0-16-introducing-check-constraint/ — Kawu, Dec 24 '19 at 08:35

NullUserException · Answer 1 · 2014-04-10T15:50:29.763

81

CHECK constraints are not supported by MySQL. You can define them, but they do nothing (as of MySQL 5.7).

From the manual:

The CHECK clause is parsed but ignored by all storage engines.

The workaround is to create triggers, but they aren't the easiest thing to work with.

If you want an open-source RDBMS that supports CHECK constraints, try PostgreSQL. It's actually a very good database.

edited Apr 10 '14 at 15:50

answered Sep 22 '11 at 22:07

NullUserException

83,810
28
209
234

1

Thank you for the short and concrete answer – Mathias Bak Sep 22 '11 at 22:07
29

Sometimes I wonder why anybody uses MySQL anymore--"oops, sorry, we decided not to implement data integrity!". If you have any sort of control of your RDBMS, and you want open source, Postgres is The Way. – Jordan Sep 22 '11 at 22:18
True that. MySQL is not a good choice for numerous reasons and this is one of them. – Dmitri Sep 14 '15 at 08:15
1

how to avoid minus value from the primary key? without `check(id>0)` function. using mysql 5.5 – 151291 Dec 09 '15 at 09:04
5

MariaDB 10.2 implements CHECK constraints. – anthonyryan1 Jul 23 '17 at 20:19
4

This answer is no longer true for MySQL version 5.8.0.16 and forward. https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html – Simon Cedergren Malmqvist Jan 13 '20 at 14:19
1

wasted 2 hours straight on this :/ – Sanket Patel Dec 16 '20 at 20:15
MySQL 8.0.16 (April 2019) and later have support for `CHECK` constraints. – Sep 03 '21 at 17:44

guzoff · Answer 2 · 2018-10-06T09:57:54.117

I don't understand why nobody here has mentioned that VIEW WITH CHECK OPTION can be a good alternative to the CHECK CONSTRAINT in MySQL:

CREATE VIEW name_of_view AS SELECT * FROM your_table
WHERE <condition> WITH [LOCAL | CASCADED] CHECK OPTION;

There is a doc on the MySQL site: The View WITH CHECK OPTION Clause

DROP TABLE `Participants`;

CREATE TABLE `Participants` (
  `meetid` int(11) NOT NULL,
  `pid` varchar(15) NOT NULL,
  `status` char(1) DEFAULT NULL check (status IN ('a','d','u')),
  PRIMARY KEY (`meetid`,`pid`)
);

-- should work
INSERT INTO `Participants` VALUES (1,1,'a');
-- should fail but doesn't because table check is not implemented in MySQL
INSERT INTO `Participants` VALUES (2,1,'x');

DROP VIEW vParticipants;
CREATE VIEW vParticipants AS 
  SELECT * FROM Participants WHERE status IN ('a','d','u')
  WITH CHECK OPTION;

-- should work
INSERT INTO vParticipants VALUES (3,1,'a');
-- will fail because view uses a WITH CHECK OPTION
INSERT INTO vParticipants VALUES (4,1,'x');

P.S.: Keep in mind that your view should be updatable! See MySQL Updatable Views (thanks Romeo Sierra for clarification in comments).

"*I don't understand why nobody here has mentioned..*" pretty much because this is rather a workaround. But of course, this resolves the problem in a very very effective manner. All you need to do is just make sure that your **view is updatable**, and voila! problem is gone. +1 for that. — Romeo Sierra, Oct 04 '18 at 11:24

score 16 · Answer 3 · answered Sep 22 '11 at 23:20

Beside triggers, for simple constraints like the one you have:

CONSTRAINT `participants_ibfk_2` 
  CHECK status IN ('a','d','u')

you could use a Foreign Key from status to a Reference table (ParticipantStatus with 3 rows: 'a','d','u' ):

CONSTRAINT ParticipantStatus_Participant_fk
  FOREIGN KEY (status)
    REFERENCES ParticipantStatus(status)

Vlad Mihalcea · Accepted Answer · 2021-01-09T15:33:30.460

Starting with version 8.0.16, MySQL has added support for 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;

score 0 · Answer 5 · answered Sep 18 '15 at 08:36

Here is a way of getting the checks you wanted quickly and easily:

drop database if exists gtest;

create database if not exists gtest;
use gtest;

create table users (
  user_id       integer unsigned not null auto_increment primary key,
  username      varchar(32) not null default '',
  password      varchar(64) not null default '',
  unique key ix_username (username)
) Engine=InnoDB auto_increment 10001;

create table owners (
  owner_id      integer unsigned not null auto_increment primary key,
  ownername     varchar(32) not null default '',
  unique key ix_ownername (ownername)
) Engine=InnoDB auto_increment 5001;

create table users_and_owners (
  id    integer unsigned not null primary key,
  name  varchar(32) not null default '',
  unique key ix_name(name)
) Engine=InnoDB;

create table p_status (
  a_status      char(1) not null primary key
) Engine=InnoDB;

create table people (
  person_id integer unsigned not null auto_increment primary key,
  pid       integer unsigned not null,
  name      varchar(32) not null default '',
  status    char(1) not null,
  unique key ix_name (name),
  foreign key people_ibfk_001 (pid) references users_and_owners(id),
  foreign key people_ibfk_002 (status) references p_status (a_status)
) Engine=InnoDB;

create or replace view vw_users_and_owners as
select 
  user_id id,
  username name
from users
union
select 
  owner_id id,
  ownername name
from owners
order by id asc
;

create trigger newUser after insert on users for each row replace into users_and_owners select * from vw_users_and_owners;
create trigger newOwner after insert on owners for each row replace into users_and_owners select * from vw_users_and_owners;

insert into users ( username, password ) values
( 'fred Smith', password('fredSmith')),
( 'jack Sparrow', password('jackSparrow')),
( 'Jim Beam', password('JimBeam')),
( 'Ted Turner', password('TedTurner'))
;

insert into owners ( ownername ) values ( 'Tom Jones'),( 'Elvis Presley'),('Wally Lewis'),('Ted Turner');

insert into people (pid, name, status) values ( 5001, 'Tom Jones', 1),(10002,'jack Sparrow',1),(5002,'Elvis Presley',1);

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

5 Answers5

Linked

Related