Can we limit the number of rows in a table in MySQL ?

Question

Is it possible to set the number of rows that a table can accommodate in MySQL ? I don't want to use any java code. I want to do this using pure mysql scripts.

Answer 1

Mysql supports a MAX_ROWS parameter when creating (and maybe altering?) a table. http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Edit: Sadly it turns out this is only a hint for optimization

"The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows."

.. Your question implied that scripts are ok; is it ridiculous to make one as simple as a cron job regularly dropping table rows above a given ID ? It's not nearly as elegant as it would've been to have mysql throw errors when something tries to add a row too many, but it would do the job - and you may be able to have your application also then check if it's ID is too high, and throw a warning to the user/relevant party.

Answer 2

I wouldn't recommend trying to limit the number of rows in a SQL table, unless you had a very good reason to do so. It seems you would be better off using a query like:

select top 1000 entityID, entityName from TableName

rather than physically limiting the rows of the table.

However, if you really want to limit it to 1000 rows:

delete from TableName where entityID not in (select top 1000 entityID from TableName)