SQL check value in current table

Question

I've got this situation: a set of hotel that has to deal with bookings. Each client can choose a room of the given hotel, from a date START_DATE to a date END_DATE.
I want to check in the table BOOKING if it's possible to book from the date START_DATE to END_DATE because there's no other booking in that period.
Here's the code:

CREATE TABLE HOTEL(
    Name varchar(30) PRIMARY KEY,
    City varchar(30)
);

CREATE TABLE ROOM(
    HotelName varchar(30) REFERENCES HOTEL (Name),
    RoomNumber int(11),
    PRIMARY KEY(HotelName , RoomNumber )
);

CREATE TABLE CLIENT(
    ClientCode char(16) PRIMARY KEY
);

CREATE TABLE BOOKING(
    HotelName varchar(30)
    RoomNumber int(11),
    ClientCode char(16) REFERENCES CLIENT(ClientCode ),
    StartDate date,
    EndDate date,
    PRIMARY KEY(HotelName , NumeroStanza, CodiceCliente, StartDate, EndDate),
    FOREIGN KEY (HotelName , RoomNumber ) REFERENCES ROOM(HotelName , RoomNumber ),
    CHECK (????)
);

How can I say: CHECK if given START_DATE < END_DATE, then START_DATE not between A and B, then END_DATE not between A and B, for every interval of date A and B already in the table?

The dialet is MySql

Many thanks

I would suggest switch to postgresql. look at http://www.postgresql.org/docs/9.4/static/rangetypes.html — venca, Jun 12 '15 at 08:46
In MySQL "The CHECK clause is parsed but ignored by all storage engines". http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working So, you are forced to use something else, like triggers. — Vladimir Baranov, Jun 12 '15 at 08:55

SQL check value in current table

0 Answers0