SQL Server: how to constrain a table to contain a single row?

I want to store a single row in a configuration table for my application. I would like to enforce that this table can contain only one row.

What is the simplest way to enforce the single row constraint ?

You make sure one of the columns can only contain one value, and then make that the primary key (or apply a uniqueness constraint).

CREATE TABLE T1(
    Lock char(1) not null,
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)

I have a number of these tables in various databases, mostly for storing config. It's a lot nicer knowing that, if the config item should be an int, you'll only ever read an int from the DB.

I usually use Damien's approach, which has always worked great for me, but I also add one thing:

CREATE TABLE T1(
    Lock char(1) not null DEFAULT 'X',
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)

Adding the "DEFAULT 'X'", you will never have to deal with the Lock column, and won't have to remember which was the lock value when loading the table for the first time.

You may want to rethink this strategy. In similar situations, I've often found it invaluable to leave the old configuration rows lying around for historical information.

To do that, you actually have an extra column creation_date_time (date/time of insertion or update) and an insert or insert/update trigger which will populate it correctly with the current date/time.

Then, in order to get your current configuration, you use something like:

select * from config_table order by creation_date_time desc fetch first row only

(depending on your DBMS flavour).

That way, you still get to maintain the history for recovery purposes (you can institute cleanup procedures if the table gets too big but this is unlikely) and you still get to work with the latest configuration.

You can implement an INSTEAD OF Trigger to enforce this type of business logic within the database.

The trigger can contain logic to check if a record already exists in the table and if so, ROLLBACK the Insert.

Now, taking a step back to look at the bigger picture, I wonder if perhaps there is an alternative and more suitable way for you to store this information, perhaps in a configuration file or environment variable for example?

I know this is very old but instead of thinking BIG sometimes better think small use an identity integer like this:

Create Table TableWhatever
(
    keycol int primary key not null identity(1,1) 
         check(keycol =1),
    Col2 varchar(7)
)

This way each time you try to insert another row the check constraint will raise preventing you from inserting any row since the identity p key won't accept any value but 1

Here's a solution I came up with for a lock-type table which can contain only one row, holding a Y or N (an application lock state, for example).

Create the table with one column. I put a check constraint on the one column so that only a Y or N can be put in it. (Or 1 or 0, or whatever)

Insert one row in the table, with the "normal" state (e.g. N means not locked)

Then create an INSERT trigger on the table that only has a SIGNAL (DB2) or RAISERROR (SQL Server) or RAISE_APPLICATION_ERROR (Oracle). This makes it so application code can update the table, but any INSERT fails.

DB2 example:

create table PRICE_LIST_LOCK
(
    LOCKED_YN       char(1)   not null  
        constraint PRICE_LIST_LOCK_YN_CK  check (LOCKED_YN in ('Y', 'N') )
);
--- do this insert when creating the table
insert into PRICE_LIST_LOCK
values ('N');

--- once there is one row in the table, create this trigger
CREATE TRIGGER ONLY_ONE_ROW_IN_PRICE_LIST_LOCK
   NO CASCADE 
   BEFORE INSERT ON PRICE_LIST_LOCK
   FOR EACH ROW
   SIGNAL SQLSTATE '81000'  -- arbitrary user-defined value
     SET MESSAGE_TEXT='Only one row is allowed in this table';

Works for me.

I use a bit field for primary key with name IsActive. So there can be 2 rows at most and and the sql to get the valid row is: select * from Settings where IsActive = 1 if the table is named Settings.

Old question but how about using IDENTITY(MAX,1) of a small column type?

CREATE TABLE [dbo].[Config](
[ID] [tinyint] IDENTITY(255,1) NOT NULL,
[Config1] [nvarchar](max) NOT NULL,
[Config2] [nvarchar](max) NOT NULL

You can write a trigger on the insert action on the table. Whenever someone tries to insert a new row in the table, fire away the logic of removing the latest row in the insert trigger code.

IF NOT EXISTS ( select * from table )
BEGIN
    ///Your insert statement
END

Here we can also make an invisible value which will be the same after first entry in the database.Example: Student Table: Id:int firstname:char Here in the entry box,we have to specify the same value for id column which will restrict as after first entry other than writing lock bla bla due to primary key constraint thus having only one row forever. Hope this helps!