how to add contraint on table using view

Question

I try to use View to create a constraint on a Mysql Table.

I have one table with cinemas sessions and one other for tickets, the goal is we can't sell tickets when the session is full.

The session has a number of places, for example, 300 places. When one ticket is sold, the number of places decreases. The rest is 299. (this is what I do with a view)

I will check before the data is written if the amount of places is enough. The condition is place_left >= number (of tickets wanted).

For example, the rest of places is 5 and a user wants 6 places, the request is rejected because the condition is not true. The condition is place_left >= number (of tickets wanted).

Actually I use two views to do that (not tested, just created), I think I can do this in one shot but I failed.

I have seen this possibility here: https://dev.mysql.com/doc/refman/8.0/en/view-check-option.html

I first tried something like that:

CREATE VIEW view_place_left AS 
SELECT sessions.id, 
    (SELECT places FROM rooms WHERE id=sessions.room_id)-(SELECT SUM(number) AS count FROM tickets WHERE session_id=sessions.id)
    AS place_left FROM sessions WHERE place_left>=0
WITH CHECK OPTION;

, but the part WHERE place_left>=0 is not working.

So I do the job in two steps

The first view:

CREATE VIEW view_place_left 
    AS SELECT sessions.id, 
    (SELECT places FROM rooms WHERE id=sessions.room_id)-(SELECT SUM(number) AS count FROM tickets WHERE session_id=sessions.id)
    AS place_left FROM sessions;

And the second view:

CREATE VIEW check_view_place_left AS
    SELECT id, place_left FROM view_place_left WHERE place_left>=0
    WITH CHECK OPTION;

That seems ok, but I think it's possible to do the job in a single view, someone can help?

I work with MySql 8.0

First Add : create sessions table :

CREATE TABLE IF NOT EXISTS sessions
(
    id VARCHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    movie_id VARCHAR(36),
    room_id VARCHAR(36),
    date DATETIME,
    FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE,
    FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
) ENGINE = InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Create tickets table :

CREATE TABLE IF NOT EXISTS tickets
(
    id VARCHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    session_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    price_id VARCHAR(36) NOT NULL,
    number INT NOT NULL,
    date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid INT,    
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (price_id) REFERENCES prices(id) ON DELETE CASCADE ON UPDATE CASCADE    
) ENGINE = InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Answer 1

SOLUTION So i found my solution. To Check and validate the place left, i just need a trigger before insert. Here is my code :

\! echo "\033[33m*** Create trigger on 'tickets'  ***\033[m";
DELIMITER $$
CREATE TRIGGER check_places BEFORE INSERT ON tickets
    FOR EACH ROW
    BEGIN
        IF PlaceLeft(NEW.session_id, NEW.number) != 1 THEN SIGNAL sqlstate '45000' set message_text = "No enough places";
    END IF;
END$$
DELIMITER ;

The function PlaceLeft

DELIMITER $$
CREATE FUNCTION PlaceLeft(session_id VARCHAR(36), number INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE places INT;
    SELECT place_left INTO places FROM view_place_left;
    IF places>=number THEN RETURN 1;
    ELSE RETURN 0;
    END IF;

END$$

Maybe that can help another lost soul :)