Constrain a table to have only one row
sql, sql-server
Solution
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
);
Problem
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