MySQL check for uniqueness of a set of attributes?-CodePudding

What I'm trying to do is, I need to perform a uniqueness validation. However, I am not simply checking "if the name iggy already exists in DB?". I need to check for uniqueness of a set of attributes.

Here's what I mean by checking for uniqueness of a set of attributes. Let's say that I need to check for the uniqueness of the columns/ attributes: type, partNum, and name. I need to check if a set of type, partNum, and name of a row is unique.

If I find two rows with the same type and name but they have different partNum, they are considered unique.
If I find two rows with the same type but they have different name and partNum, they are considered unique.
If I find two rows with the same type, name, and partNum, they are NOT unique.

id type  partNum  name
-----------------------
1  A     partA    nameA  # assume this exists
2  A     partA    nameB  # unique
3  A     partB    nameA  # unique
4  B     partA    nameA  # unique
5  A     partA    nameA  # NOT unique b/c type, partNum, and name match with 1

What is a good strategy / MySQL code to compare attribute sets from the type, partNum, and name columns?

CodePudding user response：

DISTINCT approach:

SELECT DISTINCT 
         type, partNum, name
 FROM mytable;

GROUP BY approach:

SELECT type, partNum, name
 FROM mytable
GROUP BY type, partNum, name;

GROUP BY with GROUP_CONCAT approach, if you want to see the list of id have the same uniqueness:

SELECT GROUP_CONCAT(id) AS ids, type, partNum, name
 FROM mytable
GROUP BY type, partNum, name;

Fiddle