CODEWITHSUNDEEP
mysqlconstraintscheck-constraints
Robertf
Robertf

Reputation: 21

MySQL Error Code 3815 while trying to make a check constraint

Here is the table I am trying to make a constraint for:

create table All_Events(
event_id int not null auto_increment,
event_name varchar(50),
event_type int,
event_catagory int,
other_text varchar(20),
event_description varchar(200),
start_time time,
end_time time,
event_date date,
address varchar(100),
lat decimal(10,6),
lng decimal(10,6),
contact_phone varchar(10),
contact_email varchar(50),
creator_id varchar(10),
uni_name varchar(50),
rso_name varchar(50),
primary key(event_id),
foreign key (creator_id) references Users(uid),
foreign key (uni_name) references Universities(uni_name)
);

The goal of the constraint is to make sure that events on the same day and at the same location do not have time overlaps. This is what I have so far:

alter table All_Events add constraint timeChecker
    check ((
        select count(*) from All_Events A, All_Events B where A.event_date = B.event_date and A.address = B.address and (A.start_time > B.start_time and A.start_time < B.end_time) or (A.end_time > B.start_time and A.end_time < B.end_time)
    )=0);

The problem is when trying to add the constraint I get "Error Code 3815: An expression of a check constraint 'timeChecker' contains disallowed function" but no context as to what is disallowed. Any insight or help to get this constraint working would be greatly appreciated

Upvotes: 1

Views: 783

Answers (2)

Carlos No&#233;
Carlos No&#233;

Reputation: 341

DDL is static not dynamic, therefore, you can't put queries inside the check.

If you want to make a restriction like that, you can use triggers, or pass the responsibility to the code behind.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562358

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html explains:

Subqueries are not permitted.

I suggest you read the page I linked to in its entirety.

Upvotes: 2

Related Questions