How to add a max item limit to mysql database?

Question

I want to add a limit to how many items my table can have. I want the maximum amount of items to be 10. I want it to only be 10 people in my table. I dont want it to be able to add items after the 10th person. Here is my code:

CREATE TABLE person (
name VARCHAR(233) NOT NULL,
number int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(number),
Check(number>10))

You can use triggers to achieve this http://cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql/ — Deepak Kumar T P, Jan 08 '18 at 12:12
You can also incorporate the logic into the INSERT itself. Or just insert 10 people placeholders (each with a timestamp), and then only allow UPDATEs on that table. — Strawberry, Jan 08 '18 at 12:24
See for instance, https://stackoverflow.com/questions/36270561/how-to-restrict-votes-per-day-by-ip-in-phpmysql-voting/36271685#36271685 — Strawberry, Jan 08 '18 at 12:29

score 2 · Answer 1 · answered Jan 08 '18 at 12:19

delimiter //   
 create trigger limit_persons before insert on person for each row
    begin
        declare count integer
        select COUNT(*) FROM person INTO count;
        if count>=10 then
            signal sqlstate '45000' set message_text = 'Limit exceeded';
        end if;
    end
//

score 0 · Answer 2 · answered Jan 08 '18 at 12:34

I would advise to handle such stuff as limitations in the software itself. So you have control over it later and it is overall a cleaner solution. But you can try this, if you really want to limit it in mysql:

ALTER TABLE tbl_name MAX_ROWS=10 AVG_ROW_LENGTH=nnn;

You can also check out triggers and signals:

https://dev.mysql.com/doc/refman/5.5/en/signal.html https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

score 0 · Answer 3 · answered Jan 08 '18 at 13:11

You can set up a trigger (to be specific, an Insert trigger) that counts the records and, if count is more than 10, it does not allow the insert operation.

Following code will be helpful to you,

DELIMITER $$

CREATE TRIGGER LimitRowCountTrigger
BEFORE INSERT
ON person
FOR EACH ROW
BEGIN
  SELECT COUNT(*) INTO @cnt FROM person;
  IF @cnt > 10 THEN
    CALL sth(); -- raise an error
  END IF;
END
$$

DELIMITER ;

How to add a max item limit to mysql database?

3 Answers3