CODEWITHSUNDEEP
mysql
Alison
Alison

Reputation: 249

How to add a max item limit to mysql database?

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))

Upvotes: 0

Views: 230

Answers (3)

Abhilash Ravindran C K
Abhilash Ravindran C K

Reputation: 1856

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 ;

Upvotes: 0

Ahash Pirathapan
Ahash Pirathapan

Reputation: 1

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

Upvotes: 0

Deepak Kumar T P
Deepak Kumar T P

Reputation: 1076

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
//

Upvotes: 2

Related Questions