How to limit maximum number of rows in a table to just 1

26 votes

4 answers

39782 views

                          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?
                        

Asked by Dib (447 rep)

Jun 21, 2016, 08:25 AM
Last activity: Jun 6, 2025, 05:11 PM

How to limit maximum number of rows in a table to just 1

Related Questions