是否可以将sql表限制为在设计阶段只有一行

I have a simple table as scripted 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 (Invoice number, Sales Order number etc) for the company, and as such only requires a single row. The main interaction with this table is done through stored procedures so the end use should never need to access it, but I am wondering if there is a way in SQL server to actively restrict the table to have one, and only one row, and to be able to do that at the design stage.

该表用于存储公司的关键凭证编号（发票编号，销售订单编号等），因此只需要一行。与此表的主要交互是通过存储过程完成的，因此最终用户永远不需要访问它，但我想知道SQL服务器中是否有一种方法可以主动限制表只有一行，而且只有一行，并且能够在设计阶段做到这一点。

EDIT

编辑

Proof that Gordon's suggestion works nicely

证明戈登的建议很有效

4 个解决方案

#1

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

显而易见的方法是使用insert上的触发器来确保表是空的。

I've never tried this, but an index on 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.

如果插入第二行，这应该生成唯一的密钥违例。

#2

--I think this would be cleaner and utilizes the 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]

#3

You could also use instead of trigger to accomplish the same..

你也可以使用而不是触发来完成相同的..

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

#4

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

使用以下内容定义其中一列（任何列都可以）：

a check constraint, requiring it to be the value you ultimately want
检查约束，要求它是您最终想要的值
a unique constraint
一个独特的约束

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

例如，如果您希望id列的值为1：

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

Now there can be at most 1 row and it must have id = 1.

现在最多可以有1行，它必须有id = 1。

#1