MySQL Forums
Forum List  »  Partitioning

Partitions for already existing table
Posted by: Alexander Karavaev
Date: July 29, 2011 03:43AM

Please explain how the partition can be created in already existing table

table stucture
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`added` date NOT NULL,
`username` varchar(50) NOT NULL,
`fullname` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB

now table have such records
select * from users;
+----+------------+----------+----------+
| id | added | username | fullname |
+----+------------+----------+----------+
| 1 | 2011-07-29 | aaa | AAA |
| 2 | 2011-07-29 | bbb | BBB |
| 3 | 2011-07-29 | ccc | CCC |
+----+------------+----------+----------+

I try running sql-query and received error

alter table users PARTITION BY RANGE (year(added)) (partition p2010 VALUES LESS THAN (2011), partition p2011 VALUES LESS THAN (2012), partition p2012 VALUES LESS THAN (2013) );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

How I must rebuild primary and unique keys for table?

If I will be use such primary key
PRIMARY KEY (`id`, `added`),
then the data in the table will become invalid
insert into users (id, added, username, fullname) values (1, '2000-01-13', 'qqq', 'QQQ');

+----+------------+----------+----------+
| id | added | username | fullname |
+----+------------+----------+----------+
| 1 | 2011-07-29 | aaa | AAA |
| 2 | 2011-07-29 | bbb | BBB |
| 3 | 2011-07-29 | ccc | CCC |
| 1 | 2000-01-13 | qqq | QQQ |
+----+------------+----------+----------+

Another example of integrity violation
UNIQUE KEY (`username`, `added`),

insert into users (added, username, fullname) values ('2000-01-13', 'aaa', 'QQQ');
+----+------------+----------+----------+
| id | added | username | fullname |
+----+------------+----------+----------+
| 1 | 2011-07-29 | aaa | AAA |
| 2 | 2011-07-29 | bbb | BBB |
| 3 | 2011-07-29 | ccc | CCC |
| 4 | 2000-01-13 | aaa | QQQ |
+----+------------+----------+----------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Partitions for already existing table
3291
July 29, 2011 03:43AM


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.