Oracle 表中已存重复数据的情况，如何增加唯一性约束？

Oracle 表中已存重复数据的情况，如何增加唯一性约束？

 

当 Oracle 表中已经存在重复数据时，若直接添加唯一性约束会失败，因为唯一性约束不允许表中存在重复值。以下为你介绍在这种情况下增加唯一性约束的具体步骤和方法：

方法一：删除重复数据后添加唯一性约束

步骤 1：找出重复数据

可以使用 GROUP BY 和 HAVING 子句来找出表中的重复数据。例如，假设表名为 employees，要根据 employee_id 列来判断是否重复，可使用以下 SQL 语句：

SELECT employee_id, COUNT(*)
FROM employees
GROUP BY employee_id
HAVING COUNT(*) > 1;

步骤 2：删除重复数据

找出重复数据后，需要决定保留哪一条记录，删除其余重复记录。可以使用 ROWID 来唯一标识每一行数据，以下是一个示例，假设要保留 ROWID 最大的那一行记录：

DELETE FROM employees
WHERE ROWID NOT IN (
    SELECT MAX(ROWID)
    FROM employees
    GROUP BY employee_id
);

步骤 3：添加唯一性约束

删除重复数据后，就可以添加唯一性约束了。可以使用 ALTER TABLE 语句来添加唯一性约束，示例如下：

ALTER TABLE employees
ADD CONSTRAINT unique_employee_id UNIQUE (employee_id);

方法二：通过临时表处理重复数据后添加唯一性约束

步骤 1：创建临时表

创建一个与原表结构相同的临时表，示例如下：

CREATE TABLE employees_temp AS
SELECT DISTINCT *
FROM employees;

这里使用 DISTINCT 关键字去除重复数据。

步骤 2：清空原表

清空原表中的数据，示例如下：

TRUNCATE TABLE employees;

步骤 3：将临时表数据插入原表

将临时表中的数据插入到原表中，示例如下：

INSERT INTO employees
SELECT *
FROM employees_temp;

步骤 4：添加唯一性约束

插入数据后，为原表添加唯一性约束，示例如下：

ALTER TABLE employees
ADD CONSTRAINT unique_employee_id UNIQUE (employee_id);

步骤 5：删除临时表

添加约束后，删除临时表，示例如下：

DROP TABLE employees_temp;

方法三：使用分区表处理重复数据后添加唯一性约束（适用于数据量较大的情况）

步骤 1：创建分区表

创建一个与原表结构相同的分区表，示例如下：

CREATE TABLE employees_partitioned
PARTITION BY RANGE (employee_id) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (MAXVALUE)
)
AS SELECT * FROM employees;

步骤 2：对每个分区去除重复数据

可以使用 MERGE 语句或者 DELETE 语句结合 ROWID 对每个分区去除重复数据，示例如下：

DELETE FROM employees_partitioned
WHERE ROWID NOT IN (
    SELECT MAX(ROWID)
    FROM employees_partitioned
    GROUP BY employee_id
);

步骤 3：添加唯一性约束

对分区表添加唯一性约束，示例如下：

ALTER TABLE employees_partitioned
ADD CONSTRAINT unique_employee_id UNIQUE (employee_id);

步骤 4：替换原表

可以将原表重命名，然后将分区表重命名为原表名，示例如下：

ALTER TABLE employees RENAME TO employees_old;
ALTER TABLE employees_partitioned RENAME TO employees;

以上三种方法可以根据实际情况选择使用，若数据量较小，方法一和方法二比较方便；若数据量较大，方法三可能更合适。