How to limit maximum number of rows in a table to just 1 I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as below...

ALTER TRIGGER OnlyOneConfigRow
    ON [dbo].[Configuration]
    INSTEAD OF INSERT
AS
BEGIN
    DECLARE @HasZeroRows BIT;
    SELECT  @HasZeroRows = CASE
        WHEN COUNT (Id) = 0 THEN 1
        ELSE 0
    END
    FROM
        [dbo].[Configuration];

    IF EXISTS(SELECT [Id] FROM inserted) AND @HasZeroRows = 0
    BEGIN
        RAISERROR ('You should not add more than one row into the config table. ', 16, 1)    
    END
END


This does not throw an error but is not allowing the first row to go in.

Also is there a more effective / more self explaining way of limiting the number of rows that can be inserted into a table to just 1, than this? Am I missing any built in SQL Server feature?
These two constraints would do:

CREATE TABLE dbo.Configuration
( ConfigurationID TINYINT NOT NULL DEFAULT 1,
  -- the rest of the columns
  CONSTRAINT Configuration_PK 
    PRIMARY KEY (ConfigurationID),
  CONSTRAINT Configuration_OnlyOneRow 
    CHECK (ConfigurationID = 1)
) ;


You need both the PRIMARY KEY (or a UNIQUE constraint) so no two rows have the same ID value, and the CHECK constraint so all rows have the same ID value (arbitrarily chosen to 1).
In combination, the two almost opposite constraints restrict the number of rows to either zero or one.



On a fictional DBMS (no current SQL implementation allows this construction) that allows a primary key consisting of 0 columns, this would be a solution, too:

CREATE TABLE dbo.Configuration
( -- no ConfigurationID needed at all
  -- the rest of the columns
  CONSTRAINT Configuration_PK 
    PRIMARY KEY ()                -- 0 columns!
) ;