Yea, you are correct (I should have tried it before I suggested)...
From
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html
"...All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have"
I guess that explains the need for a 'Global Partitioned Index' that you find in Oracle. It's probably not allowed yet because it would have to enforce the constraint across multiple tables (partitions)?
Given that you'd probably have to enforce the uniqueness before the insert via the application or some fancy (but probably slower) trigger/auto increment/lookup table?
IE:
Constraining Table:
CREATE TABLE "tp_unique"
(
"id" varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY ("id")
) engine=innodb;
Enforcement Trigger:
DELIMITER /
CREATE TRIGGER enforce_unique BEFORE INSERT ON tp
FOR EACH ROW BEGIN
INSERT INTO tp_unique SET id = NEW.id;
END;
/
DELIMITER ;
Showing Enforcement:
mysql> insert into tp (id,platTime) values ('thistest','2004-03-03'); Query OK, 1 row affected (0.00 sec)
mysql> insert into tp (id,platTime) values ('thistest2','2004-03-03');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tp (id,platTime) values ('thistest2','2004-03-03');
ERROR 1062 (23000): Duplicate entry 'thistest2' for key 'PRIMARY'
mysql> insert into tp (id,platTime) values ('thistest2','2007-03-03');
ERROR 1062 (23000): Duplicate entry 'thistest2' for key 'PRIMARY'
Partitioned Data:
mysql> select table_name,partition_name,partition_expression,table_rows from information_schema.partitions where table_name like 'tp%';
+------------+----------------+----------------------+------------+
| table_name | partition_name | partition_expression | table_rows |
+------------+----------------+----------------------+------------+
| tp | p0 | YEAR(platTime) | 6 |
| tp | p1 | YEAR(platTime) | 0 |
| tp | p2 | YEAR(platTime) | 0 |
| tp | p3 | YEAR(platTime) | 4 |
| tp_unique | NULL | NULL | 10 |
+------------+----------------+----------------------+------------+
mysql> select * from tp;
+-------------+---------------------+
| id | platTime |
+-------------+---------------------+
| crazytest | 1000-01-01 00:00:00 |
| test | 1000-01-01 00:00:00 |
| test2 | 1000-01-01 00:00:00 |
| test3 | 1000-01-01 00:00:00 |
| test4 | 1000-01-01 00:00:00 |
| thistest | 2004-03-03 00:00:00 |
| thistest2 | 2004-03-03 00:00:00 |
| uglytesfive | 2004-03-03 00:00:00 |
| uglytest | 1000-01-01 00:00:00 |
| uglytesttwo | 2008-03-03 00:00:00 |
+-------------+---------------------+
In practice, I'm not sure how this would affect performance, and you'd probably need a 'on delete/on update' trigger as well if you update/delete the id table.
In fact, if the id column has low cardinality, it might even keep the tp_unique table cached... (I suppose that would be a post for the performance forum)
Edited 1 time(s). Last edit at 03/23/2008 01:59PM by Phil Hildebrand.