sql-serverdatabase-designsingleton

How to constrain a table to contain a single row?

I want to store a single row in a configuration table for my application. I would like to enforce that this table can contain only one row.

What is the simplest way to enforce the single row constraint ?

Solution

  • You make sure one of the columns can only contain one value, and then make that the primary key (or apply a uniqueness constraint).

    CREATE TABLE T1(
    Lock char(1) not null,
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)

    I have a number of these tables in various databases, mostly for storing config. It's a lot nicer knowing that, if the config item should be an int, you'll only ever read an int from the DB.