I am using MySQL and I am creating a college event management database. In the database, I want to add a check constraint that prevents overlapping events when inserted into the events table. The problem I am having is that it seems like it will not allow an alias for the Events table so that I can compare the inserted row to all the other rows in the table. Is it not possible to use aliases in check constraints? ALTER TABLE Events As E ADD CONSTRAINT eventOverlap CHECK ((E.LocID=LocID) AND (E.Date=Date) AND ((End-E.Start) > 0) AND ((E.End-Start) > 0)) I have also tried using a select statement I get an error saying "Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function." ALTER TABLE Events ADD Constraint eventOverlap CHECK ( NOT EXISTS (SELECT * FROM Events E WHERE E.LocID = LocID AND E.Date = Date AND ((E.Start <= Start AND E.End > Start) OR (E.Start < End AND E.End >= End)))) OR (E.Start < End AND E.End >= End)) ) );
I am using MySQL and I am creating a college event management
ALTER TABLE Events As E
ADD CONSTRAINT eventOverlap CHECK ((E.LocID=LocID) AND (E.Date=Date) AND ((End-E.Start) > 0) AND ((E.End-Start) > 0))
I have also tried using a select statement I get an error saying "Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function."
ALTER TABLE Events
ADD Constraint eventOverlap CHECK (
NOT EXISTS
(SELECT * FROM Events E
WHERE E.LocID = LocID AND E.Date = Date AND
((E.Start <= Start AND E.End > Start)
OR (E.Start < End AND E.End >= End)))) OR (E.Start < End AND E.End >= End))
)
);
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
It still give the error:
Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function.
by Bartleby Expert
