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)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With