How to limit number of rows can be store in mysql table?

Question

I need to limit the number of rows can be insert in a MySQL Table to 10 only. I need to implement this from MySQL only i.e without using any programming language.

I have created a table with implementing MAX_ROWS = 10 but its not working.

Please check these screenshots.

I'm very confused how to do this. If someone will provide me the solution, that would be really helpful for me.

Thanks.

Answer 1

I think there is no such inbuilt functionality providede by MySQL. One solution is that you can create trigger.

CREATE TRIGGER your_trigger_name
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    DECLARE cnt INT;

    SELECT count(*) INTO cnt FROM your_table_name;

    IF cnt = 10 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can store only 10 records.';
    END IF;
END;

Try above trigger on your table. Replace your table_name with your_table_name.

Hope this will help you.