I want to restrict the maximum possible rows that can be inserted in the table. Is it possible to have such constraint in database ?
A:
Which database are you using? If it a BEFORE INSERT Trigger you can do so. Also, some databases offer extensibility.
Oracle and MySQL support them for sure, not sure about SQL Server. In the trigger, you could call a SELECT COUNT(*) FROM TABLE and then cancel if the limit is reached.
Michael Stum
2010-03-05 05:42:18
+1
A:
Depending on your DB system, you may use triggers to do this. It could count the number of records on each insert (or significantly better, cache it on insert and delete) and just reject it when it reaches a certain number.
But to me, this sounds like a very strange requirement. It would certainly be reasonably counter-intuitive (to me, anyway). Perhaps you might wish to 'archive' when a certain number has been reached, or perform some complex task. But this would be better (significantly better) done as a background task (IMHO).
Noon Silk
2010-03-05 05:43:10
A:
SQL constraints typically limit a type of insertion or a duplication, but not the amount of information being inserted.
You should be able to limit inserts from the client doing the inserting, though. On the SQL side, you could use a trigger to prevent insertion if the number of records exceeds a given amount.
Michael Todd
2010-03-05 05:43:48
A:
If this is a business rule that could change over time, I would not put the code in the database at all, but in the layer above that is calling it.
At that point you would have your business layer validate to see if the rule as been met prior to doing the insert.
Steven Dorfmeister
2010-03-05 22:05:48