Mysql - On Duplicate Key Update not working for 2 keys

Question

Hello I have a table with two unique keys profile_id and date. I don't know where is the problem but my query is not working.

Table:

CREATE TABLE `profile_views` 
    (\n  `id` int(11) NOT NULL AUTO_INCREMENT,
    \n  `profile_id` varchar(45) DEFAULT NULL,
    \n  `counter` varchar(45) DEFAULT NULL,
    \n  `date` date DEFAULT NULL,
    \n  PRIMARY KEY (`id`),
    \n  UNIQUE KEY `date_UNIQUE` (`date`),
    \n  UNIQUE KEY `profile_id_UNIQUE` (`profile_id`)\n
    ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=latin1'

Data Right Now:

# id , profile_id, counter, date
  113,      2    ,   36   , 2015-08-27

I issue this command:

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-28') 
ON DUPLICATE KEY UPDATE counter = counter+1;

And

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-27') 
ON DUPLICATE KEY UPDATE counter = counter+1;

In this query i just changed the date so it should insert new row.

My Desired Results:

If i change date still it changing the same profile id counter. I want to store everyday profile views for each profile id. So if the date and profile id is same its increment the counter otherwise insert new row.

Any help? Thanks.

Answer 1

Schema:

CREATE TABLE `profile_views` 
(  
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `profile_id` varchar(45) DEFAULT NULL,
   `counter` varchar(45) DEFAULT NULL,
   `date` date DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `date_UNIQUE` (`date`),
   UNIQUE KEY `profile_id_UNIQUE` (`profile_id`)
) ENGINE=InnoDB auto_increment=150;

insert profile_views (id,profile_id,counter,date) values (113,2,36,'2015-08-27');

... ...

select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 36      | 2015-08-27 |
+-----+------------+---------+------------+

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-28') 
ON DUPLICATE KEY UPDATE counter = counter+1;
-- 2 row(s) affected
select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 37      | 2015-08-27 |
+-----+------------+---------+------------+

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-27') 
ON DUPLICATE KEY UPDATE counter = counter+1;
-- 2 row(s) affected
select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 38      | 2015-08-27 |
+-----+------------+---------+------------+

It looks good to me. Each insert on duplicate update has a unique key clash, allowing the update to happen. What clashes? Well the unique key on profile_id does.

What am I missing?

If you lay things out step by step, people can visualize it better :>

Edit: (OP changed his mind)

CREATE TABLE `profile_views` 
(  
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `profile_id` varchar(45) DEFAULT NULL,
    `counter` varchar(45) DEFAULT NULL,
    `date` date DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `combo_thingie1` (profile_id,`date`)  -- unique composite
) ENGINE=InnoDB auto_increment=150;

insert profile_views (id,profile_id,counter,date) values (113,2,36,'2015-08-27');

... ...

select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 36      | 2015-08-27 |
+-----+------------+---------+------------+

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-28') 
ON DUPLICATE KEY UPDATE counter = counter+1;

select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 36      | 2015-08-27 |
| 150 | 2          | 1       | 2015-08-28 |
+-----+------------+---------+------------+

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-27') 
ON DUPLICATE KEY UPDATE counter = counter+1;

select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 37      | 2015-08-27 |
| 150 | 2          | 1       | 2015-08-28 |
+-----+------------+---------+------------+

Answer 2

I came up with this mad structure - it inserts new records for new dates and then updates on successive insert statements - thus incrementing the counter.

   CREATE TABLE `profile_views` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `profile_id` VARCHAR(45) NOT NULL,
        `counter` VARCHAR(45) NOT NULL,
        `date` DATE NOT NULL,
        PRIMARY KEY (`id`, `profile_id`, `date`),
        UNIQUE INDEX `profile_id_date` (`profile_id`, `date`),
        UNIQUE INDEX `id_profile_id_date` (`id`, `profile_id`, `date`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=267;