sql - check for uniqueness of COMPOSITE key

sql primary-key unique composite-primary-key
test picture test · Apr 30, 2012 · Viewed 7.3k times · Source

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

Ja͢ck picture Ja͢ck · Apr 30, 2012

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

Related questions

Can I use VARCHAR as the PRIMARY KEY?

I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a VARCHAR. My rationale is that, each coupon will have a unique code, and the only commands …

Read More
MySQL unique column string

Currently I have this table in my database: CREATE TABLE `twMCUserDB` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mc_userName` text NOT NULL, `mc_userPass` text NOT NULL, `tw_userName` text NOT NULL, `tw_userPass` text NOT NULL, PRIMARY KEY (`id`) ) …

Read More
How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More