Re: Table with one row

zeldorblat@xxxxxxxxx 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
.

Relevant Pages

  • Re: Table with one row
    ... >A simple way to avoid more than one row is to add a dummy column with a ... >unique constraint combined with a check clause. ... >> Second, to be relationally correct, the table needs a primary key. ...
    (comp.databases.theory)
  • Re: Indexes and primary keys, from Delaney
    ... > Is a unique constraint as efficient than having an unneccesary ... PRIMARY KEY constraint on the column rather than a unique index ... > claim that the sequence number is unique and never reused. ... > and account number, ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with indexes on a query
    ... All tables in a relational model should have a primary key. ... since you didn't declare a PRIMARY KEY or UNIQUE constraint ... who should know the data), I guess that the combination of VorderingId, ... defining a primary key or unique constraint always ...
    (microsoft.public.sqlserver.server)
  • Re: auto number
    ... "ABRAHAM GOLDSTEIN" wrote: ... concerned about the primary key number i hope i understood ... If you are concerned about duplicates then put a unique constraint on ... the Autonumber column; if you think you already have a unique ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Choosing Primary Key
    ... Aaron Prohaska wrote: ... > unique constraint and then a identity column as the primary key. ... > five columns that make up the unique constraint are ManufacturerID, ... I think your solution of using an IDENTITY column as the PK is the best ...
    (microsoft.public.sqlserver.programming)
Loading