How do I add a check constraint to a 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 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?

Answer 1

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.

Answer 2

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) 

Answer 3

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).

Answer 4

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);