Re: limit table to one row

p == pgmailings@codecraft.se writes:


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

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

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

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

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

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


Thanks for the info.  The data in the table in question is easy to
recreate if it gets deleted.  The main thing is to prevent more than
one row, so using CHECK( tableuniqueid = 1 ) works fine.

--
Brandon