On duplicate key based on two columns

Question

I'm having a bit of trouble with my java app,either i don't know enough or this isn't possible in a simple manner.I'm hoping it is the first option since i'm a beginner.

This is my table :

CREATE TABLE IF NOT EXISTS `evolution` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product` varchar(50) NOT NULL,
 `quantity` double NOT NULL,
 `date` date NOT NULL,
 `operator` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
) 

I want to keep track of some product sales by their date.

What i'd like to achieve is this : lets say the operator enters today, 17.01.2014 the product banana with the quantity 4.Later that day he will insert apples with the quantity 10.The same day he will insert banana with the quantity 1 and apples with the quantity 12.

Tomorrow he will do the same,insert bananas with the quantity 2 but on the date 18.01.2014.This should be on a new row since its the same product but on a different date.

In this situation, i need my database to do the following : Check if banana was inserted on 17.01.2014.If not, insert it with the quantity 0+4. If banana was already inserted on 17.01.2014 update the quantity 4+1.

The next day it will check if banana was inserted on 18.01.2014.If not,insert it with the quantity 2.If yes,update the quantity.

So : i can't have any of the columns unique since the date can be the same forapples and banana and the same for the date and of course,quantity.

How can i achieve this ? I thought about a couple of ways but it isn't very efficient:

1. Select (product,date) from evolution where product=banana AND date=18.01.2014 If this returns anything then i will execute an update. If this doesn't return anything then i will execute an insert.

2. Make a column product-date that will be unique. In it i will insert for example banana 17.01.2014.This would be easier but i don't like this way,its dirty.

All this would be so easy if i could do something like a double on duplicate key.Try and insert banana and date.Then,if the combination already exist,do an update.

To make it shorter and easier to understand:

This is valid :

+-------+----------+
|product|date      |
+-------+----------+
|banana |17.01.2014|
+-------+----------+
|banana |18.01.2014|
+-------+----------+
|apples |17.01.2014|
+-------+----------+
|apples |18.01.2014|
+-------+----------+

This is NOT valid :

+-------+----------+
|product|date      |
+-------+----------+
|banana |17.01.2014|
+-------+----------+
|banana |17.01 2014|
+-------+----------+
|banana |18.01.2014|
+-------+----------+
|banana |18.01.2014|
+-------+----------+

So they(product and date) can have duplicates by themselves but not together.

Sorry for the long post...i hope i'm not missing any easy and clean way of doing this but i just might and i sure hope so,i'm a beginner in programming. Thanks and have a good night !

Answer 1

sounds like you want a composite key on product AND date.

PRIMARY KEY (product, date)

Composite primary keys are essentially a way to determine a thing's uniqueness using more than one key. For example, if you had a store that sold only hammers and nails that were both blue or red, you couldn't identify those in the db as hammer or blue, you'd have to use both

tool  |  color |  quantity
hammer   blue     4
hammer   red      3 
nail     blue     1 
nail     red      6

your PK here would be on tool and color.