如何检查所有字段在 Oracle 中是否唯一？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4010311/
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 check if all fields are unique in Oracle?
提问by Burjua
How to check if all fields are unique in Oracle?
如何检查所有字段在 Oracle 中是否唯一？
回答by eumiro
SELECT myColumn, COUNT(*)
FROM myTable
GROUP BY myColumn
HAVING COUNT(*) > 1
This will return to you all myColumn values along with the number of their occurence if their number of occurences is higher than one (i.e. they are not unique).
如果出现次数大于 1（即它们不是唯一的），这将返回所有 myColumn 值及其出现次数。
If the result of this query is empty, then you have unique values in this column.
如果此查询的结果为空，则您在此列中具有唯一值。
回答by erbsock
An easy way to do this is to analyze the table using DBMS_STATS. After you do, you can look at dba_tables... Look at the num_rows column. The look at dab_tab_columns. Compare the num_distinct for each column to the number of rows. This is a round about way of doing what you want without performing a full table scan if you are worried about affecting a production system on a huge table. If you want direct results, do what the the others suggest by running the query against the table with a group by.
一种简单的方法是使用 DBMS_STATS 分析表。完成后，您可以查看 dba_tables... 查看 num_rows 列。看看 dab_tab_columns。将每列的 num_distinct 与行数进行比较。如果您担心影响大表上的生产系统，这是一种无需执行全表扫描即可完成所需操作的方法。如果您想要直接结果，请通过使用 group by 对表运行查询来执行其他人的建议。
回答by Randy
one way is to create a unique index. if the index creation fails, you have existing duplicate info, if an insert fails, it would have produced a duplicate...
一种方法是创建唯一索引。如果索引创建失败，则您有现有的重复信息，如果插入失败，则会产生重复...
