Query to determine if columns combine to create a unique key

Question

I'm trying to determine if a set of three columns on a table in Oracle would constitute a unique key and could be used in a 1:1 relationship.

If I run this query, and the keys are a unique combination, I should not see a count > 1, correct?

select count(*) from my_table t    
group by t.a, t.b, t.c

Is there a better/alternative way to make this determination?

Answer 1

Use the HAVING clause to easily identify duplicates.

select t.a, t.b, t.c, count(1) 
from my_table t    
group by t.a, t.b, t.c 
having count(1) > 1;

Answer 2

If the table has a decent amount of data, it's probably easier to do

SELECT t.a, t.b, t.c, count(*)
  FROM my_table t
 GROUP BY t.a, t.b, t.c
HAVING COUNT(*) > 1

If that query returns 0 rows, the three columns are (currently) unique. If that query returns 1 or more rows, you'll know which values are duplicated.

Of course, if you find that the three columns are currently unique, you'll want to create a unique constraint if you intend to make use of that fact.