Есть ли альтернатива выполнению проверочного ограничения с помощью подзапросов?

Question

У меня две таблицы

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

а также

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

Но я предполагаю, что MySQL не допускает подзапросов внутри проверок.

Есть ли альтернативный способ выполнить эту проверку? У меня есть только мимолетное знакомство с триггерами, но кажется, что они допускают только явные действия, такие как вставка, удаление и т. Д. Я просто хочу предотвратить вставку данных, если они не соответствуют критериям.

Answer 1

MySQL не применяет ограничения CHECK.

В некоторых случаях вы можете использовать ограничение внешнего ключа вместо ограничения CHECK. Например, предположим, что вы хотите ограничить room.capacity значениями от 15 до 100. В других dbms я бы написал ограничение CHECK; для MySQL я бы использовал другую таблицу и ограничение внешнего ключа.

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

В вашем случае, я думаю, что вы застряли с написанием триггеров. Во-первых, стол.

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

Один триггер обрабатывает операторы INSERT. Я не выглядел жестко; вероятно, SQLSTATE лучше, чем "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 ;

А другой триггер обрабатывает операторы UPDATE.

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 ;