How to efficiently check if two columns in the same table is one-to-one mapping?

sql-server

Solution

Assume all values are not `NULL`. The query result shows the value violates 1 to 1 relation.

-- Check Column A value which DOES NOT maps to exactly 1 Column_B value
SELECT Column_A, COUNT(Column_B) FROM MyTable GROUP BY Column_A HAVING COUNT(Column_B) > 1

-- Check Column B value which DOES NOT maps to exactly 1 Column_A value
SELECT Column_B, COUNT(Column_A) FROM MyTable GROUP BY Column_B HAVING COUNT(Column_A) > 1

Problem

Even though the distinct value counts equals, it did not necessarily mean an one-to-one mapping relation. ``` SELECT COUNT(DISTINCT [Column_A]) FROM MyTable SELECT COUNT( DISTINCT [Column_B]) FROM MyTable ``` Column A: 1 2 3 4 5 6 7 8 9 10 1 Column B: a b c d e f g h i j j The query above return a value of 10 for each column but they are not one-to-one mapping. How can I exactly check the existence of this `matching type?` Thanks in advance

Original source