SQL check unique key-CodePudding

I hope you're doing fine,

I have a problem to check if my key is unique in my table

Example

ID     NAME     DATE
1       H       12/12/2022
1       B       11/10/2012

I want to check if the ID can duplicates with other values in Name and Date or not, if all the three are duplicated it's okay, but i want to verify if i can find the same id but with another values.

Thanks

I tried to this :

SELECT ID, NAME , DATE , COUNT(ID) 
FROM TABLE t 
GROUP BY ID, NAME,  DATE
HAVING COUNT(ID) > 1

CodePudding user response：

Try this (For invalid duplicate ID)

SELECT 
    a.ID, COUNT(1) cnt 
FROM TABLE a
INNER JOIN (
    SELECT DISTINCT ID, NAME, DATE FROM TABLE
) b ON a.ID = b.ID AND a.NAME != b.NAME AND a.DATE != b.DATE
GROUP BY ID
HAVING COUNT(1) > 1

CodePudding user response：

If your database / table makes any kind of sense it has the feature of primary keys. They MUST be unique and cannot be null. So having a field that is used for IDENTIFYING an entry - thats why it is called ID - please use primary keys in your table and let the DB do it's work. https://www.w3schools.com/sql/sql_primarykey.ASP

CodePudding user response：

To find id's having invalid duplicate rows, simply do a GROUP BY, and use HAVING to find the invalid rows:

SELECT ID
FROM TABLE 
GROUP BY ID
HAVING COUNT(DISTINCT NAME) > 1
    OR COUNT(DISTINCT DATE) > 1