oracle 如何使用Oracle防止插入查询中的重复行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1655148/
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
How to prevent duplicate rows in insert query using Oracle?
提问by Gold
回答by Dani
If the id column is marked as the PKyou will not be able to insert a duplicate key, the server will throw an exception.
如果 id 列被标记为PK，您将无法插入重复键，服务器将抛出异常。
If you will insert duplicate data with different key - that's a logic that you need to deal with (like putting a unique constraint on the actual data) or do a check before you insert.
如果您将使用不同的键插入重复数据 - 这是您需要处理的逻辑（例如对实际数据设置唯一约束）或在插入之前进行检查。
回答by davek
If you mean that you have rows that are identical (apart from the primary key) and you want to know how to delete them then do:
如果您的意思是您有相同的行（除了主键）并且您想知道如何删除它们，请执行以下操作：
select col2, col3, ...coln, min(id) from A
group by col2, col3, ...coln
(I.e. select on all columns except the id.)
（即选择除 id 之外的所有列。）
To get the unique instances do
要获得唯一的实例，请执行
delete from A where id not in
(select min(id) from A
group by col2, col3, ...coln) as x
to delete all rows except the unique instances (i.e. the duplicates).
删除除唯一实例（即重复项）之外的所有行。
回答by northpole
First you would create a unique id on this table that has a sequence. Then when you insert into that table, in the insert statement you can use:
首先，您将在该表上创建一个具有序列的唯一 ID。然后，当您插入该表时，您可以在插入语句中使用：
tableName_sn.nextval
in a statement like:
在这样的声明中：
inserti into tableName (id) values (tableName_sn.nextval)
to get the next unique key in the sequence. Then if you use this key you will guarantee that it is unique. However, the caveat to that is if someone just entered a key not using the nextval function you willget primary key violations.
获取序列中的下一个唯一键。然后，如果您使用此密钥，您将保证它是唯一的。但是，需要注意的是，如果有人刚刚输入了一个不使用 nextval 函数的键，您将违反主键。
