MySQL - Check Uniqueness of Columns Values

Question

Is it possible to write a MySQL query to check the "uniquness" of a combination of columns?

At the moment I have been doing this by attempting to create unique key on the columns in phpmyadmin and seeing if it fails, which is not ideal for lots of reasons.

A query could allow me to find the rows that have the duplicate values in those columns or even count how many rows are causing the combination to not be unique. It would also be nice to know how many different sets of duplicate values there are.

Example with checking columns b, c

a|b|c
-----
1|2|3
4|1|3
1|2|9

no rows for above returned even though two values are the same in b and two values are the same in c.

a|b|c
-----
1|2|3
4|5|6
7|2|3
7|5|6

All rows would be returned or the query could return the sets 2|3 and 5|6 .

Something is either unique, or it isn't. What's 'uniqueness'? — Strawberry
– Strawberry, Commented Jun 7, 2014 at 13:37

cyon · Accepted Answer · 2014-06-07 13:45:35Z

9

A select query with group by a,b,c and having count(*) > 1 will show you rows which are duplicates of other rows. To see the unique rows you can change the having clause tohaving count(*) = 1.

If you want to check which combinations of b,c are unique you can do

select b,c
from t1
group by b,c
having count(*) = 1

For example to find duplicate a,b,c combinations you can do:

select a,b,c
from t1
group by a,b,c
having count(*) > 1

edited Jun 7, 2014 at 13:45

answered Jun 7, 2014 at 13:31

cyon

9,5684 gold badges24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

num8er · Accepted Answer · 2014-06-07 13:38:21Z

0

You better create unique table like this and then rename as You wish:

CREATE TABLE table_unique AS 
(SELECT a,b,c FROM table GROUP BY a,b,c HAVING COUNT(*)=1);

answered Jun 7, 2014 at 13:38

num8er

19.5k4 gold badges50 silver badges61 bronze badges

Collectives™ on Stack Overflow

MySQL - Check Uniqueness of Columns Values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related