How to check if all fields are unique in oracle?

Question

Hi guys simple question, but can't find anything.

Thanks

Answer 1

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...

Answer 2

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).

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

Answer 3

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.