mysql - 如何使用 DELETE 并保持参照完整性？

Question

我创建了三个表:

CREATE TABLE guest(

name varchar(100),
ranking int,
PRIMARY KEY (name)
);

CREATE TABLE room(

 roomname varchar(100),
 wallcolor varchar(100),
 rating int,
 PRIMARY KEY(roomnane)
 );

 CREATE TABLE reservation(

 name varchar(100),
 roomname varchar(100),
 day varchar(100),
 moveinday int,
 moveoutday int,
 PRIMARY KEY(roomname, day, start, finish),
  FOREIGN KEY(roomname) REFERENCES room(roomname),
  FOREIGN KEY(name) REFERENCES guest(name)
 );

我正在尝试编写一个 DELETE 查询来删除所有不符合租房条件的客人。合租即客人等级大于等于房间等级。

我试过了

DELETE FROM guest, reservations
WHERE guest.ranking<  rooms.ranking

这似乎违反了从预订到客人表的参照完整性。我该如何解决这个问题？

Answer 1

tchelidze 强调了一个案例:

Let's say guest 1 has lower rank than room1 but higher than room2. If you delete guest1 then referential integrity will be violated in reservation table (for room2 guest1 row).

我认为您不应该删除访客条目。因为该特定客人可能还有其他有效预订。

[因此，如果您删除该访客，您可能会失去您的潜在客户。损失惨重! :p]

您不应删除客人，而应删除违反您标准的相应预订条目。

这就是我想要做的。

我将删除所有客人排名低于房间排名但不包括客人本身的预订。

下面是查询:

DELETE reservation
FROM
    reservation
INNER JOIN (
    SELECT
        reservation.roomname,
        reservation.day,
        reservation.start,
        reservation.finish
    FROM
        guest
    INNER JOIN reservation ON reservation.name = guest.name
    INNER JOIN room ON reservation.roomname = room.roomname
    WHERE
        room.rating > guest.ranking
) invalidReservationTable 
ON reservation.roomname = invalidReservationTable.roomname
AND reservation.day = invalidReservationTable.day
AND reservation.start = invalidReservationTable.start
AND reservation.finish = invalidReservationTable.finish

解释: