Re: limiting number of allowed rows in a table

> On 12/28/05 9:53 AM, "Vanja" <milosevski(at)gmail(dot)com> wrote:
>
>> Can someone please tell me how can I add a constraint which will limit
>> the number of rows that are allowed to be inserted in a table?
>
> I don't think there is such a constraint (but would love to hear
> otherwise). However, you could place a trigger on a table that does
> a count(*) on the table (which might be time-consuming on a large
> table, but....) and then does whatever you want (ignore the insert,
> put it in another table, or delete an old row before inserting the
> new one). Why do you want to limit the number of rows?

That strikes me as being a terribly expensive approach.

I'd think that something more along the lines of having a column
defaulting to a sequence value would make more sense, where you'd
constrain that column to the relevant range (e.g. - from 0 to n-1),
and have a uniqueness constraint.

Efficiently finding a "free" entry is then the challenge. A
thought...

Create a *second* table, which just has the one column, limited to
range [0, n-1]. Fill that table up, at the start.

On the main table, an "on insert" trigger searches the second table
for an entry, grabs it, and deletes it. An "on delete" trigger
returns entries to the second table when records are deleted. An "on
update" trigger should prevent users from messing with the "special
column."

Each of those triggers should be of ~ O(1) cost regardless of how many
entries are free.

It would be real nice if selection of candidates from the secondary
table were done in some quasi-randomized fashion so that each
connection wouldn't normally be trying to grab the same tuple...
--
"cbbrowne","@","acm.org"
http://linuxdatabases.info/info/
Warning: Dates in calendar are closer than they appear.