Skip navigation

Using Fields as Primary Keys

What's a quick query to find out whether two fields together are unique and if I can use the combination as a primary key?

Intermediate and advanced users will know this is a simple query to write, however, I see questions like this regularly so I know it's a question that novices struggle with. Here's a simple query to get the information you want.

SELECT ColA,ColB,COUNT(*)
FROM TestTable
GROUP BY ColA,ColB
HAVING COUNT(*) > 1

In this case, the query returns a list of all unique combinations for ColA and ColB in which the combination of ColA and ColB isn't unique. Using the HAVING clause is like using a WHERE clause that's applied to the results after the GROUP BY has been applied. By saying we only want to see the result of combinations that have a count greater than 1, we'll see all value pairs for ColA and ColB that aren't unique.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish