Constrain a table to have only one row

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

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.

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
);

Use Grant to remove permissions for anyone to insert into the table after adding the one row

Your dba will be able to insert but the dba should only be running schema changes which are checked so should not be a problem in practice