Can I add a CHECK in my SQL CREATE TABLE script?

Question

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?

Why would you only want to check the exact start/end dates rather than any overlap? — Gordon Linoff, Mar 06 '20 at 15:49
I don't want that 2 different users could make a reservation for the same Place in the same days — Alberto, Mar 06 '20 at 15:51
@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. — Damien_The_Unbeliever, Mar 06 '20 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? — Alberto, Mar 06 '20 at 15:56
This is probably answered here: https://stackoverflow.com/questions/26735955/postgres-constraint-for-unique-datetime-range — Bjarni Ragnarsson, Mar 06 '20 at 16:09
Unrelated, but: `unique` is not necessary if you also use `primary key` — , Mar 06 '20 at 16:22
you are looking for an exclusion constraint: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION — , Mar 06 '20 at 16:23

Matias Barrios · Answer 1 · 2020-03-06T16:48:21.913

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.

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? — Alberto, Mar 06 '20 at 16:19

score 1 · Answer 2 · answered Mar 06 '20 at 16:40

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.

score 1 · Answer 3 · answered Mar 06 '20 at 16:56

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)

score 0 · Answer 4 · answered Mar 06 '20 at 15:57

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)

answered Mar 06 '20 at 15:57

Pandurang Choudekar

379
1
7

That answers the original question, but not his claried requirements that he doesn't want any overlap – Kevin Mar 06 '20 at 15:58
I believe, it is working for sure, even overlap also needs some extra done, but its work is done in the database side. – Pandurang Choudekar Mar 06 '20 at 16:03
@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? – Alberto Mar 06 '20 at 16:05
its SQL code which is run in the workbench query editor. You just add constraints for unique composite primary key. – Pandurang Choudekar Mar 06 '20 at 16:14

Can I add a CHECK in my SQL CREATE TABLE script?

4 Answers4