-1

I created this table for my database. 

CREATE TABLE Reservation ( Reservation_Name SERIAL UNIQUE PRIMARY KEY, User VARCHAR(64) DEFAULT 'Member', FOREIGN KEY(User) REFERENCES User(Email) ON UPDATE CASCADE ON DELETE SET DEFAULT, Location INT, FOREIGN KEY(Location) REFERENCES Place(Id_Location) ON UPDATE CASCADE ON DELETE NO ACTION, Start_Date DATE NOT NULL, Check_In TIME(1) DEFAULT '10:00:00', End_Date DATE NOT NULL, Check_Out TIME(1) DEFAULT '18:00:00', CHECK(Start_Date >= End_Date), Deleted BOOLEAN DEFAULT FALSE );

How can I insert a Check that doesn't allow to add a reservation if there's already another one with the same Start_Date and the same End_Date end the same location?

Improve this question
8
  • Why would you only want to check the exact start/end dates rather than any overlap? Commented Mar 6, 2020 at 15:49
  • I don't want that 2 different users could make a reservation for the same Place in the same days Commented Mar 6, 2020 at 15:51
  • 2
    @Alberto - but it's fine if one makes a reservation from 21st - 23rd and the other makes a reservation from 22nd - 24th? Interesting take on common concepts of reservations if so. Commented Mar 6, 2020 at 15:53
  • @Damien_The_Unbeliever Sorry, you're right. I don't want that way you said could be possible. How could I avoid this? Commented Mar 6, 2020 at 15:56
  • This is probably answered here: stackoverflow.com/questions/26735955/… Commented Mar 6, 2020 at 16:09

4 Answers 4

Reset to default
1

You can use an exclusion constraint:

CREATE EXTENSION btree_gist; ALTER TABLE reservation ADD EXCLUDE USING gist ( location WITH =, daterange(start_date, end_date, '[]') WITH && );

The extension is required so that you can create a GiST index on an integer column, and && is the "overlaps" operator for range types.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You can symply add a constraint to the table.

Alter Table Reservations Add Constraint unique_reservation Unique(Location,StartDate,EndDate);

You will need a trigger for this. Look at the above code :

CREATE TRIGGER no_overlap BEFORE INSERT ON Reservation FOR EACH ROW BEGIN SET @overlaps = ( SELECT count(*) FROM Reservation WHERE ( ( NEW.Start_Date >= Start_Date AND NEW.Start_Date <= End_Date AND NEW.Location = Location) || ( NEW.End_Date >= Start_Date AND NEW.End_Date <= End_Date AND NEW.Location = Location))); IF @overlaps > 0 THEN SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 31000, MESSAGE_TEXT = 'Unable to insert an overlapping reservation'; END IF; END; INSERT INTO Reservation (Location,Start_Date,End_Date) VALUES(1,'2020-12-13','2020-12-16'); INSERT INTO Reservation (Location,Start_Date,End_Date) VALUES(1,'2020-12-14','2020-12-17');

The first insert will succeed while the second one will fail with the corresponding error message if the dates overlap :

SQL Error [31000] [45000]: (conn=10) Unable to insert an overlapping reservation

By the way, I think you have an error in your table definition. Instead of CHECK(Start_Date >= End_Date), I think you meant CHECK(Start_Date <= End_Date),

Let me know if it helps.

Note : I did this on MariaDB but you can apply the same for any SQL DB.

Improve this answer

2 Comments

Can I also avoid that someone could make a reservation from 21st - 23rd and the other makes a reservation from 22nd - 24th for the same place?
This isn't valid Postgres syntax.
1

This works in sql server. I don't have access at the moment to check against postgres. You will need to run in two different batches after creating your table

First

CREATE FUNCTION dbo.HasOverlap ( @locationId int, @start datetime, @end datetime) RETURNS VARCHAR(5) AS BEGIN IF (SELECT count(*) FROM dbo.Reservation WHERE Location = @locationId and ( @start between Start_Date and End_Date or @end between Start_Date and End_Date or (@start <=Start_Date and @end>=End_Date ) ) ) >1 return 1 return 0 END

Second

Alter Table dbo.Reservation with check add Constraint Check_Overlap check (dbo.HasOverlap(Location, Start_Date, End_Date)=0)
Improve this answer

Comments

0

You need to use the composite primary key concept in MySQL Database. Its disable to insert duplicate items in specific columns.

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (Start_Date, End_Date, Location)
Improve this answer

4 Comments

That answers the original question, but not his claried requirements that he doesn't want any overlap
I believe, it is working for sure, even overlap also needs some extra done, but its work is done in the database side.
@DayaStark where should I write your piece of code in my exemple? And does it avoid the possibility to add two reservation in the same period for the same place?
its SQL code which is run in the workbench query editor. You just add constraints for unique composite primary key.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.