MySQL Forums
Forum List  »  Partitioning

Range partition and unique key
Posted by: Pierrick VODOZ
Date: September 24, 2014 08:42AM

Hi,
I would like to have some help but I think that my problem has no solution...

Here is the description of my table:
CREATE TABLE `full1` (
`NUM_INTERVENTION` int(13) NOT NULL DEFAULT '0',
`DATE_IT` date NOT NULL,
PRIMARY KEY (`NUM_INTERVENTION`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

When I add some lines in the table, I must check that the NUM_INTERVENTION field is unique. That's one of the reason why this is my primary key.
Indeed, I can try to add 2 lines with same NUM_INTERVENTION but different DATE_IT so the db must only accept the first row entered (only one unique NUM_INTERVENTION...)

This table is very big (more 20000000 lines) so I would like to have a range partition based on YEAR(DATE_IT)
But it seems that all column used in the parition expression (DATE_IT in my case) must be included in all unique key also in primary key.
But if I set the DATE_IT in my primary key, the db will accept multiple rows with same NUM_INTERVENTION and I don't want it.


for the moment, I assume that I can only make my own partition by creating many table like full1_2014, full1_2015, full1_2016 and manually choose the table when inserting my rows depending the year of the DATE_IT field.

For the SELECT, use the UNION operator between differents tables....

Can you confirm that the only way to solve my issue?

thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
Range partition and unique key
3077
September 24, 2014 08:42AM
1655
September 25, 2014 09:59AM
1695
September 26, 2014 03:40AM
1707
September 27, 2014 08:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.