MySQL do not allow functions in the keys, only the whole value of the column. So if you want to restrict by UNIQUE key you need another column that should contain only year and month for event. The easiest and most straightforward approach is to reduce the date value to something like YYYY-MM-01. If you have used the mysql 5.7 or newer you can declare that column 'generated' and define the function that reduce day-of-month to the constant 01 value.

+---------+-----------+------------+------------+
| user    | product   | date       | rdate      |
+---------+-----------+------------+------------+
| 1       | 1         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-08-28 | 2018-08-01 |
+---------+-----------+------------+------------+

And now you can create the UNIQUE INDEX 'permonth' (user, product, rdate). This is the uniqness established on the physical level.

On the logical level uniqness can be achieved by (at least) two common ways.

First you can define the trigger BEFORE INSERT that perform extended comparison of the dates and decline to insert the duplicate. But triggers are not very clean and easy tool because of their implicitness and my advise is to avoid triggers as possible.

The more robust way is to wrap the insert statement into the stored routine and perform call SafeVoteInsert( userID, productID, date ) instead of plain INSERT vote VALUES ...

MySQL does not allow functions in keys, only column references. So if you want to restrict by a UNIQUE key, you need another column that would contain only the year and month for the event. The easiest and most straightforward approach is to reduce the date value to something like YYYY-MM-01. If you are using MySQL 5.7 or newer, you can declare that column as generated and define an expression converting the day part to the constant 01 value.

+---------+-----------+------------+------------+
| user    | product   | date       | rdate      |
+---------+-----------+------------+------------+
| 1       | 1         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-08-28 | 2018-08-01 |
+---------+-----------+------------+------------+

And now you can create the UNIQUE INDEX permonth (user, product, rdate). This is the uniqueness established on the physical level.

On the logical level uniqueness can be achieved by (at least) two common ways.

First, you can define a BEFORE INSERT trigger that would perform extended comparison of the dates and decline to insert a duplicate. But triggers are not a very clean and easy tool because of their implicitness, and my advise is to avoid triggers if possible.

The more robust way is to wrap the insert statement into a stored routine and perform call SafeVoteInsert( userID, productID, date ) instead of plain INSERT vote VALUES ....

MySQL do not allow functions in the keys, only the whole value of the column. So if you want to restrict by UNIQUE key you need another column that should contain only year and month for event. The easiest and most straightforward approach is to reduce the date value to something like YYYY-MM-01. If you have used the mysql 5.7 or newer you can declare that column 'generated' and define the function that reduce day-of-month to the constant 01 value.

+---------+-----------+------------+------------+
| user    | product   | date       | rdate      |
+---------+-----------+------------+------------+
| 1       | 1         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-08-28 | 2018-08-01 |
+---------+-----------+------------+------------+

And now you can create the UNIQUE INDEX 'permonth' (user, product, rdate)

MySQL do not allow functions in the keys, only the whole value of the column. So if you want to restrict by UNIQUE key you need another column that should contain only year and month for event. The easiest and most straightforward approach is to reduce the date value to something like YYYY-MM-01. If you have used the mysql 5.7 or newer you can declare that column 'generated' and define the function that reduce day-of-month to the constant 01 value.

+---------+-----------+------------+------------+
| user    | product   | date       | rdate      |
+---------+-----------+------------+------------+
| 1       | 1         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-09-03 | 2018-09-01 |
| 1       | 2         | 2018-08-28 | 2018-08-01 |
+---------+-----------+------------+------------+

And now you can create the UNIQUE INDEX 'permonth' (user, product, rdate). This is the uniqness established on the physical level.

On the logical level uniqness can be achieved by (at least) two common ways.

First you can define the trigger BEFORE INSERT that perform extended comparison of the dates and decline to insert the duplicate. But triggers are not very clean and easy tool because of their implicitness and my advise is to avoid triggers as possible.

The more robust way is to wrap the insert statement into the stored routine and perform call SafeVoteInsert( userID, productID, date ) instead of plain INSERT vote VALUES ...