将具有唯一约束的行迁移到 Amazon RDS for PostgreSQL 或 Amazon Au
将具有唯一约束的行迁移到 Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL
关键要点
唯一键是一种约束，确保数据库中某一列或多列的值是独特的。PostgreSQL 对于 NULL 值的处理与其他数据库如 Oracle 和 SQL Server 不同。本文探讨如何在将数据迁移到 Amazon RDS 或 Amazon Aurora PostgreSQL 过程中，解决数据完整性问题。在关系数据库管理系统中，唯一键是指一个或多个属性列组合，能够唯一标识表中的每一行。它确保表中不会有两行在指定属性上有相同的值。唯一键的特点包括：
唯一性：唯一键确保指定属性中的每个值在表内是唯一的。没有两行可以具有相同的属性值组合。索引：数据库系统通常会为唯一键创建索引，以提升数据检索操作的性能。约束执行：唯一键约束强制指定属性的唯一性。如果尝试插入或更新重复的唯一键值，数据库系统将生成错误并阻止操作。与主键的关系：唯一键可以作为表中的主键的替代品。尽管主键唯一标识每一行并作为其他表的外键引用，但唯一键提供唯一性，与不允许 NULL 值的主键不同。在 RDBMS 中，唯一键是候选键。所有候选键能唯一识别记录，但只有其中一个被用作主键。剩下的候选键被称为唯一键，因为它们可以唯一识别关系中的记录。唯一键可以包括多列，形成多列唯一键。
在本文中，我们将展示不同数据库引擎中如何处理唯一键，并演示如何在迁移到 Amazon Relational Database ServiceAmazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL 兼容版 时处理数据完整性挑战。
PostgreSQL 中的唯一键
在 PostgreSQL 中，除了主键外，还有两种方式可以定义列或多列的数据唯一性：通过唯一索引或唯一约束。实现方式如下：
目前，仅允许建立唯一的 Btree 索引。两个 NULL 值不被视为相等。需要注意的是，PostgreSQL 15x 引入了 NULLS NOT DISTINCT稍后将讨论。多列唯一索引仅在所有索引列值在多个行中相等时拒绝记录。当为表定义唯一约束或主键时，PostgreSQL 会自动创建唯一索引。用户无法在创建表时的唯一约束中应用函数例如 COALESCE，否则会抛出错误。但是，如果单独创建唯一索引，则该函数将正常工作。以下是一个示例，考虑一个员工工资表，其中员工在初始试用期内未分配到任何部门，并且在employeename和department列上定义了唯一键。
在这种情况下，PostgreSQL 允许存储多条相同employeename且部门列为 NULL 的记录，这是有效的，因为每个 NULL 值都被视为不同。
在接下来的部分中，我们将讨论各个数据库引擎中 NULL 值的处理方式。
Oracle 中的 NULL 值
在 Oracle 中，如果唯一键的所有列都是 NULL，则表中可以有多行这样的记录。然而，如果只有部分列为 NULL，则仅允许一组 NULL 的组合。以下示例中，尝试插入多个(name6 null)的值，仅允许一个。这种行为现在可以在 PostgreSQL 15x 中通过新引入的 NULLS NOT DISTINCT 来实现。
sqlCREATE TABLE tabdemo ( id NUMBER(6) NOT NULL name VARCHAR2(20) dept VARCHAR2(25) DEFAULT D1 salary NUMBER(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id) CONSTRAINT uktabdemo UNIQUE (name dept))
INSERT INTO tabdemo(id name dept salary) VALUES (1 name1 D2 100) (2 name6 null 180) (3 null null 180) (4 null null 180)COMMIT
下面的插入语句将导致重复键冲突错误INSERT INTO tabdemo(id name dept salary) VALUES (5 name6 null 180)
错误：ORA 00001 违反唯一约束 (UKTABDEMO)
Microsoft SQL Server 和 SAP ASE/Sybase ASE 中的 NULL 值
SQL Server 以及 SAP ASE/Sybase ASE不允许在一列或多列定义为唯一时插入 NULL 值。
sqlCREATE TABLE tabdemo ( id int NOT NULL name nvarchar(20) dept nvarchar(25) DEFAULT D1 salary decimal(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id) CONSTRAINT uktabdemo UNIQUE (name dept))
INSERT INTO tabdemo(id name dept salary) VALUES (1 name1 D2 100)INSERT INTO tabdemo(id name dept salary) VALUES (2 name6 null 180)INSERT INTO tabdemo(id name dept salary) VALUES (3 null null 180)
下面的语句将产生重复键错误INSERT INTO tabdemo(idnamedeptsalary)VALUES (4nullnull180)
错误：违反唯一键约束 uktabdemo
IBM Db2 LUW 中的 NULL 值
在 Db2 LUW中，列定义为唯一时不允许存在 NULL 值。Db2 要求您为定义为唯一的列显式强制执行 NOT NULL 约束。例如，以下表定义在 Db2 中将失败：
sqlCREATE TABLE tabdemo ( id integer NOT NULL name VARCHAR(20) dept VARCHAR(25) DEFAULT D1 salary Numeric(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id) CONSTRAINT uktabdemo UNIQUE (name dept))
错误：SQL0542N 列 NAME 不能作为主键或唯一键约束的列，因为它可以包含空值
要解决此问题，必须强制执行 NOT NULL 约束，如下所示，以确保不允许 NULL 值：
sqlCREATE TABLE tabdemo ( id integer NOT NULL name VARCHAR(20) NOT NULL dept VARCHAR(25) NOT NULL DEFAULT D1 salary Numeric(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id) CONSTRAINT uktabdemo UNIQUE (name dept))
PostgreSQL 中的 NULL 值
PostgreSQL 允许在定义为唯一的列或多列唯一约束中存在 NULL 值。默认情况下，在 PostgreSQL 中，两个 NULL 值不被视为相等；然而，PostgreSQL 15x 引入了 NULLS NOT DISTINCT稍后将讨论。这意味着，即使存在唯一约束，也可能存储包含至少一个约束列中的 NULL 值的重复行。这种行为可能在与其他商业数据库如 Oracle、SQL Server 或 Db2迁移后造成数据差异。
以下示例代码：
sqlCREATE TABLE tabdemo ( id integer NOT NULL name VARCHAR(20) dept VARCHAR(25) DEFAULT D1 salary Numeric(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id) CONSTRAINT uktabdemo UNIQUE (name dept))
INSERT INTO tabdemo(id name dept salary) VALUES (1 name1 D2 100)INSERT INTO tabdemo(id name dept salary) VALUES (2 name6 null 180)INSERT INTO tabdemo(id name dept salary) VALUES (3 null null 180)
PostgreSQL 允许以下插入语句，即使在表中已存在类似记录：
sqlINSERT INTO tabdemo(id name dept salary) VALUES (4 null null 180)INSERT INTO tabdemo(id name dept salary) VALUES (5 name6 null 180)
使用基于函数的唯一索引
在这种方法中，您只允许唯一索引中定义的列只包含一个 NULL 值组合，使用内置函数。例如，可以使用 COALESCE 函数：
sqlCREATE TABLE tabdemo ( id integer NOT NULL name VARCHAR(20) dept VARCHAR(25) DEFAULT D1 salary Numeric(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id))CREATE UNIQUE INDEX uktabdemo ON tabdemo( COALESCE(name ) COALESCE(dept ))
当您尝试插入以下记录时，将因重复键冲突而失败：
sqlINSERT INTO tabdemo(id name dept salary) VALUES (11 name6 null 180)
错误：重复键值违反唯一约束 uktabdemo
使用数组基于的唯一索引
这种方法与上一种相似。在这种情况下，您使用 ARRAY 数据类型创建唯一索引，如下所示：
sqlCREATE TABLE tabdemo ( id integer NOT NULL name VARCHAR(20) dept VARCHAR(25) DEFAULT D1 salary Numeric(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id))
CREATE UNIQUE INDEX uk2tabdemo ON tabdemo ( (ARRAY[name dept]))
当您尝试插入以下记录时，将因重复键冲突而失败：
sqlINSERT INTO tabdemo(id name dept salary) VALUES (11 name6 null 180)
错误：重复键值违反唯一约束 uktabdemo2
在这两种方法中，我们能够限制数据在唯一列中仅存在一个 NULL 的组合。然而，这种做法尚未与 Oracle 结构一致，在所有的唯一键列中，NULL 值仍然是合法的情况。为了实现相同的行为，我们将考虑另一种解决方案。
使用基于 CASE 结构的唯一索引
在这种解决方案中，您可以创建使用 CASE 结构的唯一索引，如下所示。这使您能够将包含唯一列中的多个 NULL 值的记录存储到有效条目中。
sqlCREATE TABLE tabdemo ( id integer NOT NULL name VARCHAR(20) dept VARCHAR(25) DEFAULT D1 salary Numeric(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id))
CREATE UNIQUE INDEX IF NOT EXISTS uktabdemo4 on tabdemo ( ( CASE WHEN name IS NULL AND dept IS NULL THEN NULL WHEN name IS NOT NULL AND dept IS NULL THEN WHEN name IS NULL AND dept IS NOT NULL THEN ELSE name END ) ( CASE WHEN name IS NULL AND dept IS NULL THEN NULL WHEN name IS NOT NULL AND dept IS NULL THEN WHEN name IS NULL AND dept IS NOT NULL THEN ELSE dept END ))
加速器免费版永久版下载以下插入语句将成功：
sqlINSERT INTO tabdemo(id name dept salary) VALUES (1 name1 D2 100)INSERT INTO tabdemo(id name dept salary) VALUES (2 name6 null 180)INSERT INTO tabdemo(id name dept salary) VALUES (3 null null 180)INSERT INTO tabdemo(id name dept salary) VALUES (4 null null 180)
然而，以下语句将因重复键冲突而失败：
sqlINSERT INTO tabdemo(id name dept salary) VALUES (5 name6 null 180)
错误：重复键值违反唯一约束 uktabdemo4
在这个解决方案中，您可以实现Oracle 方式存储NULL列的行为，即如果所有唯一键列均为NULL，则表中可以有多行这样的记录；然而，若部分列不为 NULL，则仅允许一组 NULL 的组合。
使用 NULLS NOT DISTINCT 子句
从 PostgreSQL 15 开始，您可以在表定义中为定义为唯一的列或用于复合唯一约束的列包含 NULLS NOT DISTINCT 子句，以确保仅允许存储一个这样的组合。请参见以下代码：
sqlCREATE TABLE tabdemo ( id integer NOT NULL name VARCHAR(20) dept VARCHAR(25) DEFAULT D1 salary Numeric(8 2) CONSTRAINT pktabdemo PRIMARY KEY (id) CONSTRAINT uktabdemo UNIQUE nulls not distinct (name dept))INSERT INTO tabdemo(id name dept salary) VALUES (1 name1 D2 100)INSERT INTO tabdemo(id name dept salary) VALUES (2 name6 null 180)INSERT INTO tabdemo(id name dept salary) VALUES (3 null null 180)
以下插入将因重复键冲突而失败：
sqlINSERT INTO tabdemo(id name dept salary) VALUES (4 null null 180) 错误：重复键值违反唯一约束 uktabdemo
比较总结
以下表格总结了本文讨论的解决方案。
解决方案支持的 PostgreSQL 版本描述基于函数的唯一索引gt=11支持 SQL Server 和 SAP ASE/Sybase ASE 的唯一键行为基于数组的唯一索引gt=11支持 SQL Server 和 SAP ASE/Sybase ASE 的唯一键行为使用 CASE 结构的唯一索引gt=11支持 Oracle 的唯一键行为NULLS NOT DISTINCT 子句gt=15支持 SQL Server 和 SAP ASE/Sybase ASE 的唯一键行为结论
在本文中，我们讨论了各个数据库引擎中唯一键如何处理 NULL 值，以及在迁移至 PostgreSQL 时如何模仿或实现源数据库的功能。在插入或更新定义为唯一键的列中存在多个 NULL 值时，PostgreSQL 并不会引发警告或错误，因为两个 NULL 值不被视为相同。这可能会在迁移到 PostgreSQL 后对业务功能产生负面影响。因此，在您的架构迁移阶段，验证和应用本文描述的解决方案至关重要。
欢迎提出任何意见或问题。我们重视您的反馈！
关于作者
Sai Parthasaradhi 是 AWS 专业服务的数据库迁移专家。他与客户紧密合作，帮助他们迁移和现代化在 AWS 上的数据库。
Vikas Gupta 是 AWS 专业服务的数据库迁移首席顾问。他热衷于自动化手动流程并增强用户体验。他帮助客户迁移和现代化 AWS 云中的工作负载，特别关注现代应用架构和开发最佳实践。
评论加载中
