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