create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
constraint u_constrainte unique (id_A, id_B, id_C)
);
我希望
(id_A, id_B, id_C)在任何情况下都与众不同。因此，以下两个插入必定会导致错误：INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);
但是它的行为不符合预期，因为根据文档，两个
NULL值未与每个值进行比较其他，因此两个插入都通过且没有错误。即使在这种情况下
id_C可以是NULL，如何保证我的唯一约束？实际上，真正的问题是：我可以保证这种情况吗？ “纯sql”中的唯一性还是我必须在更高级别上实现（在我的情况下为Java）？
#1 楼
您可以在纯SQL中执行此操作。除了拥有的索引之外，还创建一个局部唯一索引：CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;
这样，您可以在表中输入
(id_A, id_B, id_C)：(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)
但是第二次都没有。
或者使用两个部分的
UNIQUE索引而没有完整的索引（或约束）。最佳解决方案取决于您的要求的详细信息。比较：使用空列创建唯一约束
尽管这对于
UNIQUE索引中的单个可空列而言既优雅又高效，但它很快就失控了不止一个。讨论这一点-以及如何使用带有部分索引的UPSERT：带有NULL值的PostgreSQL UPSERT问题
Asides
PostgreSQL中没有双引号的混合大小写标识符。
您可以将
serial列视为主键，或者将其视为Postgres 10或更高版本中的IDENTITY列。相关：自动增量表列
因此：
CREATE TABLE my_table (
my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY -- for pg 10+
-- my_table_id bigserial PRIMARY KEY -- for pg 9.6 or older
, id_a int8 NOT NULL
, id_b int8 NOT NULL
, id_c int8
, CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);
如果不这样做在表的整个生命周期（包括浪费的行和已删除的行）中，期望有20亿行（> 2147483647），请考虑使用
integer（4个字节）而不是bigint（8个字节）。评论
文档提倡使用此方法，添加唯一约束将在约束中列出的列或一组列上自动创建唯一的B树索引。不能将仅覆盖某些行的唯一性限制写为唯一性约束，但是可以通过创建唯一的部分索引来实施这种限制。
–埃文·卡洛尔（Evan Carroll）
16 Dec 2'在20:21
#2 楼
我遇到了同样的问题，我找到了在表中添加唯一NULL的另一种方法。CREATE UNIQUE INDEX index_name ON table_name( COALESCE( foreign_key_field, -1) )
因此，要回答Manual Leduc，另一个解决方案可能是
CREATE UNIQUE INDEX u_constrainte (COALESCE(id_a, -1), COALESCE(id_b,-1),COALESCE(id_c, -1) )
我认为id不会是-1。
创建局部索引的好处是什么？
如果没有NOT NULL子句，则
foreign_key_field，id_a和id_b只能为NULL一次。 >使用部分索引，这3个字段可以多次为NULL。评论
>创建部分索引有什么优势？用COALESCE完成此操作的方式可以有效地限制重复项，但是索引在查询中并不是很有用，因为它的表达式索引可能与查询表达式不匹配。也就是说，除非您选择COALESCE（col，-1）...，否则您将不会找到索引。
– Bo Jeanes
16年8月5日在4:58
@BoJeanes尚未针对性能问题创建索引。创建它是为了满足业务需求。
–Luc M
16年8月5日在19:51
#3 楼
Null可能意味着该行当前未知，但将来会添加（如果正在运行，请添加示例FinishDate），或者该行不能应用任何值（例如，黑色则示例Project洞EscapeVelocity）。我认为通常最好通过消除所有Null来标准化表格。
在您的情况下，您希望在列中允许
Star您只希望允许一个NULLs。为什么？这两个表之间是什么关系？也许您可以简单地将列更改为
NULL并存储而不是NOT NULL，这是一个永远不会出现的特殊值（例如NULL）。这将解决唯一性约束问题（但可能会有其他可能不希望的副作用。例如，使用-1表示“未知/不适用”将使列上的总和或平均值计算产生偏差。或者所有此类计算都必须考虑特殊值并忽略它。）评论
在我的情况下，NULL实际上是NULL（例如，id_C是table_c的外键，因此它不能具有-1值），这意味着它们在“ my_table”和“ table_c”之间没有关系。因此它具有功能上的含义。顺便说一下[[（1，1,1，null），（2，1,2，null），（3,2,4，null）]是插入数据的有效列表。
– Manuel Leduc
2011-12-28 9:40
它实际上不是SQL中使用的Null，因为在所有行中只需要一个。您可以通过将-1添加到table_c或通过添加另一个表（将是子类型table_c的超类型）来更改数据库模式。
–超立方体ᵀᴹ
2011-12-28 9:48
我只想向@Manuel指出，此答案中关于null的观点并不是普遍存在的，并且存在很多争议。像我一样，许多人认为null可以用于您想要的任何目的（但对于每个字段都只意味着一件事，并应在字段名称或列注释中记录）
–杰克·道格拉斯（Jack Douglas）
2011-12-29 7:03
当列为FOREIGN KEY时，不能使用虚拟值。
–Luc M
2012年5月17日18:42
+1我和你在一起：如果我们希望某些列组合是唯一的，那么您需要考虑一个实体，其中该列组合是PK。 OP的数据库架构可能应该更改为父表和子表。
–A-K
13年11月11日在22:23
评论
因此，假设您在（A，B，C）列中具有值（1,2,1）和（1,2,2）。是否应允许添加（1,2，NULL）？A和B不能为null，但C可以为null或任何正整数值。因此（1,2,3）和（2,4，null）是有效的，但（null，2,3）或（1，null，4）是无效的。并且[[1,2，null），（1,2,3）]不会破坏唯一约束，但是[[1,2，null），（1,2，null）]必须打破它。
有没有永远不会出现在这些列中的值（例如负值？）
您不必在pg中标记约束。它将自动生成名称。仅供参考。