21

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?

  • 2
    Just as an explanation for why your original approach wasn't working: You use an Instead Of trigger, which means your code is run instead of the insert statement. So in order for the insert to happen, you have to explicitly include it as part of the trigger. – Scott M Jun 22 '16 at 17:11
51

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!
) ;
24

You could define the ID as a computed column evaluating to a constant value, and declare that column to be unique:

CREATE TABLE dbo.Configuration
(
  ID AS CAST(1 AS tinyint),  -- or: AS bit
  ...  -- other columns
  CONSTRAINT UQ_Configuration_ID UNIQUE (ID)
);
9

You can also use trigger..

create trigger LimitTable
on YourTableToLimit
after insert
as
    declare @tableCount int
    select @tableCount = Count(*)
    from YourTableToLimit

    if @tableCount > 50
    begin
        rollback
    end
go
1

Seems a bit of an odd requirement but ho-hum :) You could just have a constraint on the table and then only allow updates (no insert or deletes) to the table?

CREATE TABLE dbo.Config (
    ID INT identity(1,1), 
    CONFIGURATION VARCHAR(MAX),
    constraint ck_limitrows CHECK (ID <=1) 
    );

It's a bit of a hackey way to do it though, would it not be better to just enforce changes to configuration through a stored procedure which can then handle all this logic for you?

  • 2
    Just ensure no-one can delete from the table. If someone deletes and then tries to re-insert it will try to insert with an identity of 2 which it won't allow. – Mat Jun 21 '16 at 8:46
  • 5
    This does not forbid the ID to have a value of 0 or a negative one. And as @Mat points, it will fail if you try to insert another row if the first one is deleted. – ypercubeᵀᴹ Jun 21 '16 at 9:13
  • 2
    As for being an "odd requirement", I prefer using a single-row table for configuration settings, instead of the seemingly more common EAV design. The advantage of the former being that columns can be created with an appropriate data type and appropriate constraints can be added (more easily). – Kenny Evitt Jun 21 '16 at 13:36
  • 2
    Perhaps I wasn't very clear in my previous comment. A side effect of "This does not forbid the ID to have a value of 0 or a negative one" is that the table could end with 2 or more rows. The identity property does not imply unique constraint. – ypercubeᵀᴹ Jun 22 '16 at 8:43
  • 3
    To illustrate what @ypercubeᵀᴹ said, with this solution you can do e.g. INSERT INTO dbo.Config DEFAULT VALUES; just once, but you can follow it with SET IDENTITY_INSERT dbo.Config ON; INSERT INTO dbo.Config (ID) VALUES (0); SET IDENTITY_INSERT dbo.Config OFF; many times, and you'll end up with a multiple row table. – Andriy M Jun 22 '16 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.