Re: Table with one row

zeldorblat_at_gmail.com wrote in news:1129734437.042073.223980 @f14g2000cwb.googlegroups.com:

> In our system we have a table with one row ("today") that is used
> solely to tell the application what the current processing date is (as
> well as yesterday and tomorrow). The table looks like this:
>
> create table today (
> yesterday smalldatetime not null,
> today smalldatetime not null,
> tomorrow smalldatetime not null,
>
> check ((yesterday < today) and (today < tomorrow))
> )
>
> Since the table only has one row, we purposely join to it without a
> condition (cartesian join), knowing that we won't get any more rows
> than we expect. The table is updated daily by one of our batch jobs.
>
> My question is twofold. First, how does one restrict the number of
> rows in a table (in this case, to guarantee there's only one row) ? I
> suppose you can use a trigger, but perhaps there's a better way.

A simple way to avoid more than one row is to add a dummy column with a unique constraint combined with a check clause. Something like

... ,dummycol integer default 1 not null check (dummycol = 1) unique, ...

(Will not prohibit zero rows...)  

> Second, to be relationally correct, the table needs a primary key.
> What might the key be in this situation? It seems silly to add an
> identity column since
>

Why not use the dummycol as PK? (And then you can remove the NOT NULL and unique constraints.)

HTH,
Jarl Received on Wed Oct 19 2005 - 17:01:59 CDT