CODEWITHSUNDEEP
sqlsqlite
Neigyl R. Noval
Neigyl R. Noval

Reputation: 6038

Best way to limit the number of records in table in sql

I have a users table. The number of users should be limited to 100. That is, once the number of users reaches 100, I cannot add any user anymore.

The first thing I have in mind is to query first for the number of rows returned by the select * from users. If the number of rows returned is < 100, I can still add more users. Else, I can't anymore.

That would take 2 queries to meet my needs. Any idea how to have it in only 1 query? I have come across into using the trigger statement but I would like to know if it is a good idea. Please add code snippet as an example.

Upvotes: 0

Views: 220

Answers (3)

FoolishSeth
FoolishSeth

Reputation: 4021

This isn't really a normal way to use an RDBMS, so that's why there's not a simple way to do it.

Probably the way I would do this is to write a stored procedure and call it instead of the insert. The stored procedure would essentially encapsulate your two-query solution but at the database layer. (EDIT - no sprocs in sqlite)

However it's important to consider why you need to limit the number of users. If it's a situation where you can only have 100 active users at a time, then I would strongly consider instead storing all of the users, but also storing a state indicating whether they are active or not. That gives you the opportunity to move inactive users to active and vice versa... that's just one scenario.

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

As the comments say, best way to handle this through front end..

Here is another option..

If you have an Identity column in the table , you could add a constraint for this table..

ALTER TABLE <your_table>
ADD CONSTRAINT chk_users
CHECK( <identity_column> < 100)

Upvotes: 2

Andrew Walters
Andrew Walters

Reputation: 4803

I think it's always a good idea to have the database make sure the data is clean if it can do so.

A double layer approach where you have the trigger to make sure data is good and check in the application too might be good.

Upvotes: 0

Related Questions