You can have an INDEX that is NOT UNIQUE. But you can’t have more than ONE PRIMARY INDEX, and that PRIMARY INDEX should be UNIQUE.
UNIQUE = there is no duplicate (in the same column). You can’t consider 4 columns being unique as a whole for a PRIMARY INDEX.
I don’t know if I answered your question?..
For example, if I do:
SELECT * FROM data_day WHERE device_id = 1175214041;
it should always return 0 or 1 result, even if the other columns are different as a whole.
The idea behind a PRIMARY INDEX is that it identifies each row uniquely. Let’s imagine you have a list of prices, and your items have a bar code. The bar code is a PRIMARY INDEX because you can only have one price per bar code (even if the packaging changes, or the name of the product changes, or where it comes from changes; it doesn’t matter, because we’re talking about the same product).
What cause the crash is not the INSERT, it’s the DELETE. I don’t know how you make your query, but I guess MySQL wants to delete every row that as the same device_id (or any of the other 3 PRIMARY INDEXes being the same), resulting in a crash. When mySQL uses a PRIMARY INDEX for deleting, only ONE ROW can be deleted… whereas your table may contains more than a row with the same device_id (or more than a row with the same timestamp, etc).
Imagine that I ask you: “delete the product which has the bar code 1175214041”, then you start to search on your list “which product has the bar code 1175214041?” and you end up with more than a single result, which one would you delete? (as you can only delete one) eek: … => crash :o
UNIQUE(device_id, timestamp,data_type,object) means:
- device_id contains no duplicate ;
- timestamp contains no duplicate ;
- data_type contains no duplicate ;
- object contains no duplicate.
UNIQUE(device_id, timestamp,data_type,object) doesn’t mean: “there’s only ONE UNIQUE row having a defined: device_id AND timestamp AND data_type AND object”.