CODEWITHSUNDEEP
mysqlmariadbconstraints
Xenon
Xenon

Reputation: 311

Methods for conditional insertion into tables (MariaDB)

This question deals with how one should handle conditional insertions into tables.

Suppose we customers and employees. A customer can only be assigned 4 employees at a time. We will come back to this in a moment.

On the database level, we have checks and triggers.

In MariaDB, CHECK constrains cannot have subqueries, so we cannot impose constraints regarding degree of participation in this way. For instance, we cannot say something like

CHECK ( customer_id IN (SUBQUERY that returns count of Employees with == 4 employees) ).

Triggers may be the solution. insert-or-update). If a manager attempts to assign a customer to an employee who already has 4 customers, we should not allow that record to be inserted into the table that links customers to their employees. In this case we want a trigger that acts before insertion. We only want the insertion to occur if that employee is not in the subquery that lists employees with 4 customers.

We want to stop the insertion, but by law, the trigger will not do this based on a condition that is merely stated in the trigger. From my understanding, the only way to do this is to send a signal (look at Use a trigger to stop an insert or update

This leads to my next two questions.

Is using a signal 'ideal'? Is it problematic? Is there a better way to insert into a table based on a condition, instead of merely performing side effects prior to an insertion or perhaps after an insertion?

It appears that the db would send a signal if the constraint was violated to begin with, so would this ever impact the application built on top of it?

Upvotes: 0

Views: 55

Answers (1)

Rick James
Rick James

Reputation: 142296

Some flavors of restraint can be dealt with thus:

INSERT INTO TABLE (a,b,c)
    SELECT ...
        WHERE <-- put the logic here (if possible)

That is, arrange to have the SELECT deliver more or fewer rows based on your business logic.

Upvotes: 1

Related Questions