oracle 具有重复数据的主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16146558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Primary Keys with Duplicated data?
提问by user2307250
I am trying to assign a primary key but some of the data is duplicated? How can we keep the data but still have a primary key?
我正在尝试分配一个主键，但某些数据是重复的？我们如何保留数据但仍然有主键？
回答by Walter Mitty
The only time I ran into this in 17 years of database work, was due to an error in the data. A single customer had been entered twice, with the same id, into a table that had no primary key.
在 17 年的数据库工作中，我唯一一次遇到这种情况是由于数据错误。一个客户被两次使用相同的 id 输入到一个没有主键的表中。
When it was desired to declare a primary key, it was necessary to clear up the data error first, in order to be able to use the most logical column as a primary key.
当需要声明主键时，需要先清除数据错误，才能使用最符合逻辑的列作为主键。
So my advice is to see whether all the data in your table conforms to the business rules as well as checking to see if the business rules imply using this column as a primary key.
所以我的建议是看看你表中的所有数据是否都符合业务规则，并检查业务规则是否暗示使用此列作为主键。
回答by APC
A primary key is a series of unique values. So you can keep your duplicate data or you can have a primary key, but not both.
主键是一系列唯一值。因此，您可以保留重复数据，也可以拥有主键，但不能同时拥有。
Alternatively you can use a surrogate key, by adding an ID column to the table and populating it from a sequence. Of course, in this situation the "primaruy key" is meaningless because you're not enforcing the uniqueness of the business key. But at least you can fool yourself that you have realtional integrity.
或者，您可以使用代理键，方法是向表中添加 ID 列并从序列中填充它。当然，在这种情况下，“主键”没有意义，因为您没有强制执行业务键的唯一性。但至少你可以自欺欺人地相信你有真实的完整性。
回答by NathanTempelman
From what I can guess, you have data, and you're trying to assign a column as the primary key, however the data has duplicate values for that column.
根据我的猜测，您有数据，并且您试图将一列指定为主键，但是该数据具有该列的重复值。
Primary keys need to be unique. That's databases 101. If the only thing that works as a primary key is a duplicate, you're not structuring your tables properly.
主键必须是唯一的。那是数据库 101。如果唯一可以用作主键的是重复项，那么您就没有正确构建表。
回答by Marco Baldelli
You can manually create a non-unique index on the columns to be constrained, then add a primary key constraint with the novalidateclause, eg:
您可以在要约束的列上手动创建非唯一索引，然后使用novalidate子句添加主键约束，例如：
create table t1 (x number);
insert into t1 (x) values (1);
insert into t1 (x) values (1);
commit;
create index t1_pk on t1 (x);
alter table t1 add (
constraint t1_pk primary key (x) novalidate
);
I must stress however that, although possible, this is usually bad design!
然而，我必须强调，尽管可能，这通常是糟糕的设计！
回答by André Silva
Primary keys are unique, so if you want to have duplicate data in a certain column, that same column can't be the primary kay of that table.
主键是唯一的，所以如果你想在某个列中有重复的数据，同一列不能是该表的主键。
