Re: Single Row Table?

--- Scott Marlowe <smarlowe(at)qwest(dot)net> wrote:

> On Sun, 2004-08-29 at 15:30, Bruno Wolff III wrote:
> > On Fri, Aug 27, 2004 at 13:32:07 +0530,
> > Yateen Joshi <yjoshi(at)starentnetworks(dot)com> wrote:
> > > Hi,
> > >
> > > I have got a table which is supposed to contain
> only one row. It does
> > > not have any primary keys defined.
> > > So, essentially, when a new insert happens in
> that table, I would like
> > > it (the insert) to fail if there is already a
> row existing in that
> > > table.
> > > How can I do that? Can I add any constraints? Or
> do I need to write a
> > > separate trigger for the same?
> >
> > A simple way to force this is to add a primary key
> and a constraint
> > that forces the primary key to be a particular
> value.
>
> Is it reasonable / possible to add a check
> constraint something like
> select count(*) from table <=1?

ISTM most natural to do this with a rule, e.g.:

CREATE RULE my_insert_rule AS ON INSERT TO my_table DO
INSTEAD NOTHING;

Which will cause all inserts to be silently dropped.
If you want to return a message to the application,
you could use a statement trigger, which I believe we
don't have yet, or you could use a rule like:

CREATE RULE my_insert_rule AS ON INSERT TO my_table DO
INSTEAD SELECT 'Inserts to my_table not allowed!';

Although the application may not be expecting a return
message, and might not handle it.

>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
> joining column's datatypes do not match
>

_______________________________
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
http://promotions.yahoo.com/goldrush