mysql - 使用完整性增强的预订表

Question

我目前正在尝试解决一个问题，但我陷入了困境。以下是给出的。我正在尝试使用完整性增强来创建预订表，但在约束条件下，同一房间不能重复预订。

酒店(酒店编号，酒店名称，城市) 房间(房间编号，酒店编号，类型，价格) 预订(酒店编号，宾客编号，日期从，日期到，房间编号) 宾客(宾客编号，宾客名称，宾客地址)，其中酒店包含酒店详细信息，酒店编号是首要的关键。 Room 包含每个酒店的房间详细信息，并且 (roomNo, hoteINo) 构成主键。 Booking 包含预订详细信息，并且 (hoteINo、guestNo、dateFrom) 构成主键。 Guest 包含客人详细信息，guestNo 是主键..

这是我已经走了多远:

CREATE TABLE Booking(
    hotelNo     HotelNumbers        NOT NULL,
    guestNo GuestNumbers        NOT NULL,
    dateFrom    BookingDate     NOT NULL,
    dateTo      BookingDate     NULL,
    roomNo      RoomNumber      NOT NULL,
    PRIMARY KEY (hotelNo, guestNo, dateFrom),
    FOREIGN KEY (hotelNo) REFERENCES Hotel 
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (guestNo) REFERENCES Guest 
        ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY (hotelNo, roomNo) REFERENCES Room
        ON DELETE NO ACTION ON UPDATE CASCADE,
    CONSTRAINT RoomBooked
    CHECK (NOT EXISTS ( SELECT *
                FROM Booking b
                WHERE b.dateTo > Booking.dateFrom AND
                b.dateFrom < Booking.dateTo AND
                b.roomNo = Booking.roomNo AND
                b.hotelNo = Booking.hotelNo)),
    CONSTRAINT GuestBooked
    CHECK (NOT EXISTS ( ...............)

Answer 1

我认为，您需要从 4 个字段对 Booking 进行 pk:hotelNo、guestNo、dateFrom、roomNo；并且您必须像这样修改 CONSTRAINT RoomBooked:

CREATE TABLE Booking(
hotelNo     HotelNumbers        NOT NULL,
guestNo GuestNumbers        NOT NULL,
dateFrom    BookingDate     NOT NULL,
dateTo      BookingDate     NULL,
roomNo      RoomNumber      NOT NULL,
PRIMARY KEY (hotelNo, guestNo, dateFrom, roomNo),
FOREIGN KEY (hotelNo) REFERENCES Hotel 
    ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (guestNo) REFERENCES Guest 
    ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (hotelNo, roomNo) REFERENCES Room
    ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT RoomBooked
CHECK (NOT EXISTS ( 
 SELECT 1
 FROM Booking b
 WHERE (
  isnull(b.dateTo, b.dateFrom) 
   between Booking.dateFrom and isnull(Booking.dateTo, Booking.dateFrom)
  or b.dateFrom 
   between Booking.dateFrom and isnull(Booking.dateTo, Booking.dateFrom))
 AND b.roomNo = Booking.roomNo 
 AND b.hotelNo = Booking.hotelNo)))

我认为，一位客人可以预订多于一间房间，因此您无法为 GuestBooked 设置约束