Partitions for already existing table
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 |
+----+------------+----------+----------+