如何在Oracle数据库中为现有表添加唯一约束？

Question

现有一张三列组成主键的表。

如何最好地为其添加一个唯一列？最好在添加时创建一个序列。

- rapadura

---

你是指将唯一列添加到主键，还是只是表格？ - DCookie

添加一个列并在该列上添加唯一约束。由于第一张表的主键由三个列组成，因此我无法在另一张表上创建外键约束。因此，我想创建一个唯一列，以便可以从另一张表中引用它。 - rapadura

Answer 1

您可以使用 ALTER TABLE 命令向表中添加额外的列。

ALTER TABLE table_name
  ADD( new_column_name NUMBER UNIQUE );

您可以创建一个新的序列，然后创建一个触发器，使用该序列填充新列。

CREATE SEQUENCE sequence_name;

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.new_column_name := sequence_name.nextval;
END;

如果你使用的是11g版本之前的Oracle，那么你的触发器需要从DUAL进行SELECT，以便填充新列:new_column_name而不是直接赋值。

CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT sequence_name.nextval
    INTO :new.new_column_name 
    FROM dual;
END;

假设在插入新行之前，您还希望使用序列值初始化所有现有行

UPDATE table_name
   SET new_column_name = sequence_name.nextval
 WHERE new_column_name IS NULL

但是，在现有的具有复合主键的表中添加一个新的序列生成列似乎非常奇怪，除非目标是使用该新列作为主键。拥有一个序列生成列的整个意义在于您拥有一个稳定的、合成的主键，不依赖于实际的业务数据。因此，似乎更有意义的做法是删除现有的主键，添加新列，填充数据，声明新列为新主键，然后定义由旧主键组成的三个列的唯一约束条件。

- Justin Cave

---

我之前不知道可以在修改表时添加“unique”关键字，因为我认为空值都是相同的，所以不会是唯一的...谢谢Justin！ - rapadura

我会按照你的建议去做，使用唯一键来引用其他表确实很奇怪，而主键却毫无作用。 - rapadura

UNIQUE限制的特点是它们可以包含NULL值。因此，在已有数据的表中添加唯一列比添加主键列更容易（尽管后者也不那么棘手）。 - APC

有趣的是：SQL Server在这个问题上的行为与Oracle不同。唯一约束列仅包含一个null，假设一个列唯一约束。Bravid的好帖子展示了一个带脚本的例子。https://forums.oracle.com/forums/thread.jspa?threadID=374915 - Atilla Ozgur