Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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 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))     ) );

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question

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))

    )

); 

 

SQL File 8* xrso X X x BZTA 1. ALTER TABLE Events 2 3 4 5 6 7 events # 3 users 15 college-event.events Limit to 1000 rows 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))))| Output ************** 0 Action Output Time Action 1 23:08:03 ALTER TABLE Events ADD CONSTRAINT eventOverlap CHECK ( NOT EXISTS ( SELECT * ... 2 23:20:56 ALTER TABLE Events ADD CHECK (NOT EXISTS (SELECT * FROM Events E WHERE E.LocID = Lo... 3 23:21:37 ALTER TABLE Events ADD Constraint eventOverlap CHECK (NOT EXISTS (SELECT FROM Events ... 191 SQLAdditions ***** ► Ip Jump to Automatic context help i disabled. Use the toolbar manually get help for the current caret position or toggle automatic help. > Context Help Snippets Message Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function. Error Code: 3815. An expression of a check constraint 'events_chk_2' contains disallowed function. Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function. Duration / Fetch 0.063 sec 0.063 sec 0.000 sec
Transcribed Image Text:SQL File 8* xrso X X x BZTA 1. ALTER TABLE Events 2 3 4 5 6 7 events # 3 users 15 college-event.events Limit to 1000 rows 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))))| Output ************** 0 Action Output Time Action 1 23:08:03 ALTER TABLE Events ADD CONSTRAINT eventOverlap CHECK ( NOT EXISTS ( SELECT * ... 2 23:20:56 ALTER TABLE Events ADD CHECK (NOT EXISTS (SELECT * FROM Events E WHERE E.LocID = Lo... 3 23:21:37 ALTER TABLE Events ADD Constraint eventOverlap CHECK (NOT EXISTS (SELECT FROM Events ... 191 SQLAdditions ***** ► Ip Jump to Automatic context help i disabled. Use the toolbar manually get help for the current caret position or toggle automatic help. > Context Help Snippets Message Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function. Error Code: 3815. An expression of a check constraint 'events_chk_2' contains disallowed function. Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function. Duration / Fetch 0.063 sec 0.063 sec 0.000 sec
SQL File 8* x rso events users college-event.events 14 TAUS 1 x ALTER TABLE Events As E 2 ADD CONSTRAINT eventOverlap CHECK ((E.LOCID=LOCID) AND (E.Date-Date) AND ((End-E.Start) > 0) AND ((E.End-Start) > 0)) 53 Limit to 1000 rows Q1
Transcribed Image Text:SQL File 8* x rso events users college-event.events 14 TAUS 1 x ALTER TABLE Events As E 2 ADD CONSTRAINT eventOverlap CHECK ((E.LOCID=LOCID) AND (E.Date-Date) AND ((End-E.Start) > 0) AND ((E.End-Start) > 0)) 53 Limit to 1000 rows Q1
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

It still give the error:

Error Code: 3815. An expression of a check constraint 'eventOverlap' contains disallowed function.

Solution
Bartleby Expert
SEE SOLUTION
Knowledge Booster
Transaction Processing
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS