CODEWITHSUNDEEP
mysqlconditional-statements
Marley
Marley

Reputation: 185

MySQL Inserts with condition

I am wondering if there is a way to restrict MySQL INSERTs on a given condition, for example:

We have a table with projects and another table with employees. The maximum allowed people in a project is restricted to 5. How can we do this in MySQL?

Thanks in advance, Marley

Upvotes: 0

Views: 75

Answers (3)

Kickstart
Kickstart

Reputation: 21513

Possibly use a trigger to check the number of users prior to insert:-

DELIMITER $$

CREATE TRIGGER project_before_insert_allow_only_five_employees
     BEFORE INSERT ON project FOR EACH ROW
     BEGIN
          IF (SELECT COUNT(employee_id) FROM project WHERE project_id=NEW.project_id) > 4
          THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: only 5 employees on a project';
          END IF;
     END;

Not tested, and assuming that your project table has a row per employee (easy enough to modify if the project id is instead stored on the employees table for those associated with it).

Upvotes: 0

Max Frai
Max Frai

Reputation: 64276

You have to read about constraint, check (or value based) type.

CREATE TABLE tbl (
   ...
   numPeople INT CHECK (numPeople <= 5)
   ...
)

Yeah, sorry, check constraint doesn't work in MySQL

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

You can't enforce this constraint via the schema without breaking the relation (i.e. having 5 employees columns). MySQL does not allow CHECK constraints.

You can SELECT the number of employees separately before doing the query, or you can use INSERT ... SELECT with the restriction.

INSERT INTO Employees
    (employee, column, names)
SELECT
    ?, ?, ?
FROM
    Projects
    NATURAL JOIN Employees
WHERE
    projID = ?
GROUP BY
    projID
HAVING
    COUNT(empID) < 5

Upvotes: 1

Related Questions