CODEWITHSUNDEEP
mysqlconstraintsworkbenchintegrity
TheWalkingFroggy
TheWalkingFroggy

Reputation: 23

How to add a integrity check on mySQL Workbench

I'm new to mySQL and I'd like to add a integrity check (or constraint? Sorry I'm italian) in my database. Let me explain: I have two tables

Workshop (location, numPlaces, numOperations)
Operation (idoperation, workLocation, ...)

numPlaces represents the maximum number of operations the workshop can afford. I created a trigger that everytime I insert a new record in Operation, the numOperations of Workshop referred to that specific location is increased by 1.

Now what I'd like to do is: when numOperations = numPlaces, if I try to insert a new record to Operation, the system must tell me that I can't. Basically, it can't be possible that numOperations > numPlaces

Is there a way to achieve that? Sorry if I can't provide codes, but I literally have no idea where should I go to create these types of CHECKS. Hope you can help me!

Upvotes: 1

Views: 574

Answers (1)

nbk
nbk

Reputation: 49373

For this to work, you must have set the workshop with the correct number of places. And you should have a routine, that diminishes the number of operations so that you can enter new operation in one workshop

CREATE TABLE Workshop 
(location Text, numPlaces int , numOperations int
)

INSERT INTO Workshop VALUES ('A',9,8)

CREATE TABLE Operation (idoperation int, workLocation Text)

CREATE TRIGGER before_Operation_insert
BEFORE INSERT
ON Operation FOR EACH ROW
BEGIN
    DECLARE Placescount_ INT;
    DECLARE Operationscount_ INT;
    
    SELECT numPlaces, numOperations
    INTO Placescount_,Operationscount_
    FROM Workshop WHERE location = NEW.workLocation;
    
    IF Placescount_ < Operationscount_ THEN
        UPDATE Workshop
        SET numOperations = numOperations + 1  WHERE location=new.workLocation ;
    ELSE
        SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Maximum Number of Operarations reached in location ';
    END IF; 

END

INSERT INTO Operation VALUES (1,'A')

Maximum Number of Operarations reached in location

db<>fiddle here

Upvotes: 1

Related Questions