Re: limit table to one row

On 4 jun 2009, at 22.17, Richard Broersma wrote:

> On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
> <brandon(at)geronimoalloys(dot)com> wrote:
>> Is there a way when creating a table to limit it to one row? That
>> is,
>> without using a stored procedure?
>
>
> Sure just add a check constraint along the lines of:
>
> CONSTRAINT Only_one_row
> CHECK( tableuniqueid = 1 ); --assuming you row has a unique id
> of 1

Another way, which I've used a couple of times, is to use the rule
system:

CREATE TABLE single_row (value text);
INSERT INTO single_row VALUES ('value');
CREATE RULE no_insert AS ON INSERT TO single_row DO INSTEAD NOTHING;
CREATE RULE no_delete AS ON DELETE TO single_row DO INSTEAD NOTHING;

This way, the table must have exactly one row. I believe the
constraint check would still allow the row to be deleted, which you
may or may not want.

If you want an error to be raised when inserting or deleting, you'd
have to call a function raising the error in the rule. A minor
drawback is that the table still isn't safe from TRUNCATE though.

Sincerely,

Niklas Johansson