MySQL Forums :: Partitioning :: Range partition and unique key


Advanced Search

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 1874 Pierrick VODOZ 09/24/2014 08:42AM
Re: Range partition and unique key 959 Rick James 09/25/2014 09:59AM
Re: Range partition and unique key 993 Pierrick VODOZ 09/26/2014 03:40AM
Re: Range partition and unique key 1036 Rick James 09/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.