在alter table中创建约束，而不检查现有数据 [英] Create constraint in alter table without checking existing data

问题描述

我尝试在Oracle 11g R2随附的 OE.PRODUCT_INFORMATION 表中创建约束。
约束应该使 PRODUCT_NAME 是唯一的。

我试过了以下语句：

  ALTER TABLE PRODUCT_INFORMATION 
 ADD CONSTRAINT PRINF_NAME_UNIQUE UNIQUE（PRODUCT_NAME）; 
  

问题是，在 OE.PRODUCT_INFORMATION 名称当前存在两次以上。
执行上面的代码会引发以下错误：

  alter table验证约束失败，因为表有
重复键值。 
  

有可能不会在现有表数据上使用新创建的约束吗？
我已经尝试过 DISABLED 关键字。但是当我启用约束时，我会收到相同的错误消息。

解决方案

如果你正在寻求强制某种唯一性所有未来条目，同时保留您当前的重复项，而不能使用UNIQUE约束。

您可以使用表上的触发器来检查要根据当前表值插入的值如果已经存在，则阻止插入。

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

或者您可以删除重复的值，然后提供UNIQUE约束。

编辑：在Jonearles和Jeffrey Kemp的意见之后，您实际上可以使用 NOVALIDATE 子句对具有重复值的表启用唯一约束，但是您不能在该约束列上具有唯一索引。 p>

查看Tom Kyte的解释此处。

但是，我仍然担心这个意图对未来支持数据库的人有多明显。从支持的角度来看，删除重复项或使用触发器使您的意图变得更清楚是更明显的。
YMMV

I'm trying to create a constraint on the OE.PRODUCT_INFORMATION table which is delivered with Oracle 11g R2. The constraint should make the PRODUCT_NAME unique.

I've tried it with the following statement:

ALTER TABLE PRODUCT_INFORMATION
  ADD CONSTRAINT PRINF_NAME_UNIQUE UNIQUE (PRODUCT_NAME);

The problem is, that in the OE.PRODUCT_INFORMATION there are already product names which currently exist more than twice. Executing the code above throws the following error:

an alter table validating constraint failed because the table has
duplicate key values.

Is there a possibility that a new created constraint won't be used on existing table data? I've already tried the DISABLED keyword. But when I enable the constraint then I receive the same error message.

解决方案

If you are looking to enforce some sort of uniqueness for all future entries whilst keeping your current duplicates you cannot use a UNIQUE constraint.

You could use a trigger on the table to check the value to be inserted against the current table values and if it already exists, prevent the insert.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

or you could just remove the duplicate values and then enfoce your UNIQUE constraint.

EDIT: After Jonearles and Jeffrey Kemp's comments, I'll add that you can actually enable a unique constraint on a table with duplicate values present using the NOVALIDATE clause but you'd not be able to have a unique index on that constrained column.

See Tom Kyte's explanation here.

However, I would still worry about how obvious the intent was to future people who have to support the database. From a support perspective, it'd be more obvious to either remove the duplicates or use the trigger to make your intent clear. YMMV

这篇关于在alter table中创建约束，而不检查现有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！