sql - check for uniqueness of COMPOSITE key

Question

Can somebody please help me with this difficulty I am having?

I would like to check some data whether it is valid, so a small part of the validation consists of entity integrity where I check that my primary key is unique

SELECT order_id, COUNT(order_id)
FROM temp_order
GROUP BY order_id
HAVING ( COUNT(order_id) > 1 )

in this case, order_id is the primary key. This query works fine.

The problem:

I now have another table temp_orditem which has a composite primary key made up of 2 fields: order_id, product_id.

How can I check whether the primary key is unique (i.e. the combination of the 2 fields together)? Can I do the following?

SELECT order_id, product_id, COUNT(order_id), COUNT(product_id)
FROM temp_order
GROUP BY order_id, product_id
HAVING ( COUNT(order_id) > 1 AND COUNT(product_id)>1)

Answer 1

I would just write this:

SELECT order_id, product_id, COUNT(*) AS x
FROM temp_order
GROUP BY order_id, product_id
HAVING x > 1

Answer 2

This is the query you need:

select order_id, product_id, count(*)
from temp_order
group by order_id, product_id
having count(*) > 1