finnw finnw - 1 month ago 7
SQL Question

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

Answer

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.

Comments