如何只允许表格中有一行？

Question

我有一张表，只想要一个条目。因此，如果有人试图插入另一行，则不应允许，只有在删除先前存在的行后才能插入。

如何为这样的表设置规则？

- X-jo

Answer 1

一个“UNIQUE”约束允许多行具有null值，因为两个null值被认为不相同（除非在Postgres 15或更高版本中使用“NULLS NOT DISTINCT”）。类似的考虑也适用于“CHECK”约束。它们允许表达式为true或null（但不能为false）。同样，null值可以通过检查。为了排除这种情况，可以将列定义为“NOT NULL”，或者将其设置为主键，因为主键列会自动定义为“NOT NULL”。参见：为什么可以在可为空的列上创建具有主键的表？此外，只需使用布尔值。

CREATE TABLE public.onerow (
   onerow_id bool PRIMARY KEY DEFAULT true
 , data text
 , CONSTRAINT onerow_uni CHECK (onerow_id)
);

CHECK约束对于boolean列来说可以非常简单。只允许true。

您可能希望 REVOKE（或不GRANT）DELETE和TRUNCATE权限从public（和所有其他角色）中移除，以防止单行被删除。例如：

REVOKE DELETE, TRUNCATE ON public.onerow FROM public;

- Erwin Brandstetter

Answer 2

在表中添加一个新列，然后对该列添加检查约束和唯一性约束。例如：

CREATE TABLE logging (
    LogId integer UNIQUE default(1),
    MyData text,
    OtherStuff numeric,
    Constraint CHK_Logging_singlerow CHECK (LogId = 1)
);

现在您只能拥有一个LogId = 1的行。 如果尝试添加新行，则会破坏唯一性或检查约束。

（我可能搞砸了语法，但这给了您一个想法？）

- Richard Hansell

---

“UNIQUE”约束允许（多个）“NULL”值，因此这是不安全的。详情请参见我的答案。 - Erwin Brandstetter

好的，我检查了一下，PostgreSQL和SQL Server的工作方式不同；SQL Server只允许一个NULL值（在我看来这是一个更明智的实现）。无论如何，这都是无关紧要的，因为我们的想法是在创建新行时不指定LogId，这将把值设置为默认值1，它不是NULL，所以并不重要？然而，如果我们非常严谨，我想定义应该是NOT NULL :P - Richard Hansell

有两种数据库管理员：一种是“拘泥于细节”的，另一种则在 Stack Overflow 上发布关于神秘“破损”数据库的绝望问题。 :p - Erwin Brandstetter

Answer 3

您应该在该表上创建一个 ON BEFORE INSERT 触发器。在触发器上，调用一个过程来检查 count(*)，当计数为1时，返回异常消息给用户，否则插入将被允许继续。

请参考此文档中的示例。

- CodeNewbie

---

可能性是存在的，但比必要的更昂贵和复杂。 - Erwin Brandstetter

你的方式确实可行，但是更加昂贵。我只是提供了一个不需改变表格即可实现的解决方案。 - CodeNewbie

当这不是你将要插入的表格（除了一次之外，假设这种表格的目的是存储单例配置）时，这个事情的昂贵性为什么会成为问题呢？从美学上讲，对我来说肯定更加愉悦，因为你不需要与存储的数据无关的列和检查。 - Dmitry Minkovsky

Answer 4

我认为这里不需要额外的字段。只需要在常量上创建唯一索引即可：

CREATE UNIQUE INDEX ux_onerow ON onerow ((0));

- barsuk

---

这是一种非常好的方法，但如果您需要执行“ON CONFLICT”操作（例如，upsert），那么您将不得不使用一个人工列。 - Александр Чи

Answer 5

您可以使用以下方法强制单行显示：

id int GENERATED ALWAYS AS (1) STORED UNIQUE

这将强制id列始终为1，并且由于它是一个UNIQUE列，表只能容纳这一行。

DB Fiddle

- enriquejr99