How to check if all fields in oracle are unique?

Question

Hi guys, a simple question, but nothing to find.

thanks

Answer 1

SELECT myColumn, COUNT(*) FROM myTable GROUP BY myColumn HAVING COUNT(*) > 1 

This will return all myColumn values ​​along with the number of their cases if their number of occurrences is more than one (that is, they are not unique).

If the result of this query is empty, then you have unique values ​​in this column.

Answer 2

An easy way to do this is to parse the table using DBMS_STATS. After that, you can look at dba_tables ... Look at the num_rows column. Take a look at dab_tab_columns. Compare num_distinct for each column with the number of rows. This is a round-robin way to do what you want without doing a full table scan if you are worried about the impact on the production system of a huge table. If you want direct results, do what others offer by doing a query on a table using a group.

Answer 3

one way is to create a unique index. if index creation fails, you have duplicate information, if the insert failed, it would create a duplicate ...