Answered: I am using MySQL and I am creating a…

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

Related questions

Concept explainers

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

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

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

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

by Bartleby Expert

SEE SOLUTION

Knowledge Booster

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