'Table/Trigger design to prevent time overlaps in booking table using SQL

I'm trying to design a table (or possibly trigger) which consists of a roomNum, startTime, endTime, and a groupName. It is supposed to keep track of which group (represented by groupName) books which room and when. I have the create table statement as such:

CREATE TABLE ProjectRoomBookings
(roomNum INTEGER,
startTime INTEGER,
endTime INTEGER,
groupName CHAR(10),
PRIMARY KEY (roomNum, startTime),
CONSTRAINT Check_time CHECK (startTime<endTime));

For simplicity we express times in the 24h military-style format, eg. 9 for 9AM, 17 for 5PM, etc. and use only whole hours (eg. 9:30 not valid, only 11,12,13...etc. valid). The Primary Key is a combination of the roomNum and startTime. So we can have entries like

roomNum startTime endTime groupName
~~~~~~~ ~~~~~~~~~ ~~~~~~~ ~~~~~~~~~
1.      2         3       A
1       4         5       B

However, a new entry (for a new group) could be accidentally put in to occupy a room, even before the existing group in that same room is done using it. As seen here:

roomNum startTime endTime groupName
~~~~~~~ ~~~~~~~~~ ~~~~~~~ ~~~~~~~~~
1.      2         4       A
1       3         4       B

How will I redesign the table (or write a trigger) to avoid SQL inserts like the second record above.

I have already checked this out, but my composite key preferably needs to remain a combination of the roomNum and startTime.

PS - New to SQL. Some help would be much appreciated.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source