CODEWITHSUNDEEP
sqlsql-server
SecretIndividual
SecretIndividual

Reputation: 2519

Validating primary key uniqueness

I got a table for which I need to check that there are no fault entries that do not abide to the primary key that runs over 2 columns.

I am kinda stuck trying to write a SQL query to do this. As in, I would not know where to even begin.

Does any of you know how I can write a SQL query that checks whether there are any duplicate entries in a table for a combination of column X and Y?

Upvotes: 0

Views: 1178

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can get duplicates in two columns using:

select x, y
from t
group by x, y
having count(*) > 1;

This is unnecessary if (x, y) is declared as a primary key. Duplicate values cannot be inserted into the table with this declaration.

In SQL Server, this is also true if (x, y) is declared as unique. In some databases, null values are allowed to be duplicated with a unique constraint/index; however, SQL Server does not allow duplicate NULL values for unique constraint/indexes.

Upvotes: 1

Related Questions