Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql - check for uniqueness of COMPOSITE key

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)
like image 803
test Avatar asked Apr 30 '12 21:04

test


1 Answers

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
like image 91
Ja͢ck Avatar answered Sep 23 '22 22:09

Ja͢ck