Is there an alternative to performing a check constraint with subqueries?

Question

I have two tables

Table Room(
  capacity INTEGER,
  roomID varchar(5)
)

and

Event(
  attendance INTEGER,
  room varchar(5), 
  CHECK(attendance <= (SELECT R.capacity FROM Room R, WHERE R.roomID = room))
)

But I guess MySQL doesn't allow for subqueries inside of checks.

Is there an alternative way to perform this check? I only have a passing familiarity with triggers, but it seems like they only allow for explicit actions like insert, delete, etc. I just want to prevent the data from being inserted unless it fits the criteria.

MySQL parses check clauses but ignores them: **The CHECK clause is parsed but ignored by all storage engines.**, source: [CREATE TABLE](https://dev.mysql.com/doc/refman/5.6/en/create-table.html) — VMai, Aug 09 '14 at 20:00

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-08-09T21:12:15.087

MySQL doesn't enforce CHECK constraints.

In some cases, you can use a foreign key constraint instead of a CHECK constraint. For example, let's say you wanted to constrain room.capacity to values between 15 and 100. In other dbms, I'd write a CHECK constraint; for MySQL, I'd use another table and a foreign key constraint.

create table capacities (
  capacity integer,
  primary key (capacity)
);

-- Assumes that all the values between 15 and 100
-- are valid capacities. But that's unlikely.
insert into capacities values
(15), (16), (17), (18),
-- . . .
(100);

create table room(
  capacity INTEGER,
  roomID varchar(5),
  primary key (roomID),
  foreign key (capacity) references capacities (capacity)
);

In your case, I think you're stuck with writing triggers. First, the table.

create table event (
  eventID integer not null,
  attendance integer not null,
  roomID varchar(5) not null,
  primary key (eventID),
  foreign key (roomID) references room (roomID)
);

One trigger handles INSERT statements. I didn't look hard; there's probably a better SQLSTATE than '22003'.

delimiter $$

create trigger check_capacity_on_insert
before insert on event
for each row begin
if(new.attendance) > (select capacity from room where room.roomid = roomid) then
    signal sqlstate '22003' set message_text = 'Value out of range for room.capacity';
end if; 
end;

$$

delimiter ;

And another trigger handles UPDATE statements.

delimiter $$

create trigger check_capacity_on_update
before update on event
for each row begin
if(new.attendance) > (select capacity from room where room.roomid = roomid) then
    signal sqlstate '22003' set message_text = 'Value out of range for room.capacity';
end if; 
end;

$$

delimiter ;

Is there an alternative to performing a check constraint with subqueries?

1 Answers1