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

user_37367 0
Viewed 32473

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 ?

database-design sql-server singleton
12 Answers

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

user_165164570

The easiest way is to define the ID field as a computed column by value 1 (or any number ,....), then consider a unique index for the ID.

CREATE TABLE [dbo].[SingleRowTable](
  [ID]  AS ((1)),
  [Title] [varchar](50) NOT NULL,
   CONSTRAINT [IX_SingleRowTable] UNIQUE NONCLUSTERED 
   (
      [ID] ASC
   )
) ON [PRIMARY]
user_41041670
Related