Constrain a table to have only one row

Question

What's the cleanest way to constrain a SQL table to allow it to have no more than one row?

This related question discusses why such a table might exist, but not how the constraint should be implemented.

So far I have only found hacks involving a unique key column that is constrained to have a specific value, e.g. ALWAYS_0 TINYINT NOT NULL PRIMARY KEY DEFAULT (0) CONSTRAINT CHECK_ALWAYS_0 CHECK (ALWAYS_0 = 0). I am guessing there is probably a cleaner way to do it.

The ideal solution would be portable SQL, but a solution specific to MS SQL Server or postgres would also be useful

Answer 1

The cleanest way (I think) would be an ON INSERT trigger that throws an exception (thus preventing the row from being inserted). This also gives the client app a chance to recover gracefully.

Answer 2

I just solved the same problem on SQL Server 2008 by creating a table with a computed column and putting the primary key on that column:

CREATE TABLE MyOneRowTable (
    [id] AS (1) PERSISTED NOT NULL CONSTRAINT pk_MyOneRowTable PRIMARY KEY,
    -- rest of the columns go here
);