Is it possible to limit the sql table to only one row at the design stage

Question

I have a simple table as shown below.

CREATE TABLE dbo.KeyNumbers ( RowId INT IDENTITY ,ProFormaNumber NCHAR(10) NULL ,SalesOrderNumber NCHAR(10) NULL ,PurchaseOrderNumber NCHAR(10) NULL ,InvoiceCreditNoteNumber NCHAR(10) NULL ,InvoiceNumber NCHAR(10) NULL ,PurchaseInvoiceCreditNoteNumber NCHAR(10) NULL ,ModifiedDate DATETIME NULL ,CONSTRAINT PK_KeyNumbers PRIMARY KEY CLUSTERED (RowId) ) ON [PRIMARY] 

The table is used to store key document numbers (account number, customer order number, etc.) for the company and, as such, only one line is required. The main interaction with this table is performed using stored procedures, so end users will never need to access it, but I wonder if there is a way on the SQL server to actively limit the table to one and only one row, and also be able to do this is at the design stage.

EDIT

Proof that Gordon's proposal works great

Answer 1

The obvious method uses a trigger to insert to make sure the table is empty.

I have never tried this, but an index in a computed column might also work:

 alter table dbo.KeyNumbers add OneAndOnly as ('OneAndOnly'); alter table dbo.KeyNumbers add constraint unq_OneAndOnly unique (OneAndOnly); 

This should generate a unique key violation if a second row is inserted.

Answer 2

- I think it would be cleaner and use existing columns

 CREATE TABLE dbo.KeyNumbers ( RowId AS (1) PERSISTED ,ProFormaNumber NCHAR(10) NULL ,SalesOrderNumber NCHAR(10) NULL ,PurchaseOrderNumber NCHAR(10) NULL ,InvoiceCreditNoteNumber NCHAR(10) NULL ,InvoiceNumber NCHAR(10) NULL ,PurchaseInvoiceCreditNoteNumber NCHAR(10) NULL ,ModifiedDate DATETIME NULL ,CONSTRAINT PK_KeyNumbers PRIMARY KEY CLUSTERED (RowId) ) ON [PRIMARY] 

Answer 3

You can also use instead of a trigger to accomplish the same thing.

 create table test ( id int ) create trigger dbo.test_trgr on dbo.test instead of insert as begin --here we check table rows,if there are no rows,iinsert..else ignore if not exists(select 1 from dbo.test) begin insert into dbo.test select * from inserted end 

Answer 4

Define one of the columns (any column will do) with:

• a check , requiring that the value you ultimately want
• a unique limitation

For example, if you want the id column to have a value of 1 :

 create table mytable ( id int check (id = 1) unique, othercol1 text, othercol2 int // etc ) 

Now there can be no more than 1 line, and it should have id = 1.