'How to fix Errors when adding Check function: 3815 or 3814. An expression of a check constraint contains disallowed function

I have a problem with MYSQL. I have one table "screening" that consist of scrreningID(PK), movieID (FK) and roomID (FK). I want to make a check that if the movie is3d and room canplay3d, insert function can be executed. Otherwise, if movie is not3d and the room canplay3d then the insert cannot be done.

I try to alter my tabla and add a Check constraint. I created a Function for the Check constraint. However, It is always getting Error

Here is my function Code :

CREATE DEFINER=`root`@`localhost` FUNCTION `is3d`(mov int, roo int) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
declare movie_3d integer;
declare room_3d integer;

select is3d into movie_3d from movie where movieID = mov; 
select canplay3d into room_3d from room_auditorium where roomID = roo;

if (movie_3d = 1 && room_3d =1) then
RETURN 1;
else
return 0;
end if;

END

The function is successful and it works well. But when I add it to the Check constraint 

alterter table screening_show add constraint 
checkisthreedy check (is3d(movieID, roomID) = 1)

it always shows an error:

Error Code: 3814. An expression of a check constraint xonstrainname contains disallowed function: CustomerLevel.

I also tried to make basic functions, but the result is still the same when I use it for Check constraint.

mysqlsqlfunction


Solution 1:[1]

I faced the same problem and when I checked the MYSQL user manual I found this.

CHECK condition expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

  1. Stored functions and user-defined functions are not permitted.
  2. Stored procedure and function parameters are not permitted.

You can refer more about the check constraint at THIS LINK

Solution 2:[2]

The only way to solve this I see is calling the function manually like this:

insert into table_name (a, b, ?)
select 1, 2, 3
where is3d(1, 1) = 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Roshinie Jayasundara
Solution 2 Eugene

Related Questions