0

How can I set a limit of rows to a mysql table? (I am using php)

POSSIBLE METHOD-1:

When the user tries to SIGN-UP I can select everything from the TABLE and get the number of ROWS in the TABLE and check if it is == 100 and if so echo "Could not sign you up. Cause: Max user limit reached!";, or if it is < 100 to allow the user to SIGN-UP. But is there an easier method to use like just setting a max row limit for the TABLE?

Improve this question
8
  • Why?...What is your main goal? Commented Jun 6, 2022 at 11:29
  • I'm tryn to set a max limit of users in a db each row is a user and has a user id fname, lname, username, password, email. So essentially I'm trying to limit the rows to a max of 100 rows which is 100 users. Commented Jun 6, 2022 at 11:44
  • Any help is appreciated Commented Jun 6, 2022 at 11:45
  • So, what do you expect to happen when someone inserts the 101st row? Commented Jun 6, 2022 at 11:47
  • I know since I am using php when the user tries to signup I can select everything from the table and get the row_count and see if it is == 100 and if so echo cant signup, max user limit reached, or if it is below 100 to allow the user to signup... But is there an easier way to use like just setting a max row limit Commented Jun 6, 2022 at 11:47

2 Answers 2

Reset to default
1

There is no feature in MySQL to declare a limit on the rows in a table.

The CREATE TABLE statement has an option MAX_ROWS=N but it doesn't enforce a limit. It's a hint to the storage engine of how many rows you are likely to insert, so the storage engine it can allocate storage or initialize data structures.

To enforce a limit of 100 rows, you could make a table with integers from 1 to 100, and in your signup table, make a foreign key to that table of integers.

CREATE TABLE numbers ( num TINYINT UNSIGNED PRIMARY KEY) ); -- fill the above table with values 1 to 100... CREATE TABLE SignUp ( id TINYINT UNSIGNED PRIMARY KEY, email VARCHAR(80) NOT NULL, FOREIGN KEY (id) REFERENCES numbers (num) );

The id must be unique, you can only use each number on one row. Therefore the table can only have 100 rows.

It's up to your client app to pick an unused number, then insert it. This is somewhat harder than it sounds, since you could have more than one user trying to sign up at the same time, so both might choose to use the same number. So the app has to catch errors and retry even after trying to choose an unused number.

Improve this answer
1
  • Thanks, but I think I'll stick with my current solution. Commented Jun 7, 2022 at 4:12
0

Possible realizations.

  1. Trigger BEFORE INSERT.

The trigger checks the amount of rows in a table. If it is greater or equal to the rows limit then SIGNAL 45000 statement is executed, the insertion fails, and you may detect this.

When you need to alter this limit then you must rewrite the trigger (if the limit is hardcoded) or edit this limit value in some service table. This method allows to set the limit less then actual rows amount is.

  1. Using UPDATE instead of INSERT.

Needed amount of rows is inserted into the table by the administrator. Identifying column is set to NULL in these rows.

When you need to insert a row you execute UPDATE which updates the values in a row with NULL using the values to be inserted, and LIMIT 1 is used. When you need to delete a row then you update the row to be deleted and set this identifying column value to NULL.

When all rows are set and no empty place for new row then UPDATE updates 0 rows, and you may detect this.

When you need to alter the limit then you simply add or delete according empty rows amount.

Improve this answer
1
  • Thanks but I think I'll stick with my current solution. Commented Jun 7, 2022 at 4:10