How to restrict the number of rows in a table.

Question

How to restrict the number of rows in a table.

pravarap

SSC Enthusiast

Points: 163
More actions
December 17, 2008 at 5:52 am

#199009

I have one table. that table should contain only one row. how to add this kind of constraints on tables.
Please do the needful.:)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I've used a couple ways like instead of triggers on insert and delete. The problem there is that you can only update the row (which is the design in this case I think). However I once found myself locking myself out in the create script of the table because I ran the create trigger before I populated the table. It's a small gotcha but still a funny anecdote. 😛

Another way I'm thinking right now that could allow the delete / inserts / updates to run without blocking would be to add a PK column (unique not null) and also add a constraint that the pk = 1.

Since the column cannot be null or different than 1, then only 1 row can exists in the table. However that leaves the delete open (might be a problem and break connecting applications).

See what you need and use what fits best.

pravarap SSC Enthusiast Points: 163 More actions · Answer 2

thanks a lot:), i think second option is applicable. as adding triggers will make my application more complicated.:P

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Not really... the check constraint error will be meaningless. You can't add a comment in the constraint but you can in the table (assuming you let the dev use SSMS).

Now if you use a trigger, you can put a nice error message like 'Table xyz can only containt 1 row, deletes and inserts are forbidden (trigger name)'.

That saved me a lot of grief over the years. Yes it's more hidden, but it'll make itself clearly show if and only if needed.

pravarap SSC Enthusiast Points: 163 More actions · Answer 4

Ok. thanks for the quick response and your valuable suggestion.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Now that I had the night to sleep it over, I'd actually implement both.

The trigger fires the user-friendly warning, then when doing DML in the table to change whatever, the check constraint would garantee that the data remains valid (assuming you plug in the right numbers of course, but that's a given).

That way all bases are fully covered and everybody is a happy camper.

The last thing I'd do is never disable the trigger (even to code changes). That way it's impossible to break the application unless entering bad data.