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

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