SQL query for finding a set of columns that create a unique key for a table

Question

I have a table with a dozen or so columns and no primary key defined for it. There are several million rows stored in it. Is there a SQL query I could use that would return the combination of columns that will give a unique key for the table? It is specifically an Oracle database.

Answer 1

First, it seems like the wrong way of approaching the problem. I would start by investigation what is supposed to be unique according to the business rules.

Nevertheless, you will have to investigate the power set of column combinations. The cardinality of the power set on n is 2^n, but the empty set can be ignored. So if you have 10 columns this means 2^10-1=1023 combinations to investigate. It's probably easiest to use a host language to generate the sql, but you can use group by cube to get the combinations. Assuming columns c1, c2, c3:

  with t(a,b,c) as ( values ('c1','c2','c3') ) 
  select a,b,c 
  from t group by cube (a,b,c)

A  B  C 
-- -- --
-  c2 c3
-  -  c3
-  c2 - 
-  -  - 
c1 c2 - 
c1 -  - 
c1 -  c3
c1 c2 c3

Now you can generate the sql by looping over this result set:

select c2,c3 from t group by c2,c3 having count(1) > 1

if no rows exists the column combination is a candidate for a key. Just verify that no sub set of this combination is also a candidate.