"A PRIMARY KEY need to include all fields in the partition function."
This means: Any column used in the partitioning expression must be part of the table's primary key, if it has a PK.
Here are your options:
1. Make client_id the table's primary key.
2. Don't use a primary key for this table.
3. Make client_id part of the table's primary key.
Let's look at each option:
1. If you need to alloiw duplicate values for client_id, this obviously won't work.
2. Having a primary key is nice, but not 100% necessary. There are other ways to guarantee uniqueness and get the benefits of an index. However, you can't just omit the primary key, create a unique index on sid_id, and then use client_id in the partitioning expression. This is because - if the table has no primary key but does have a unique key - then the same rule applies to the unique key, and any columns used in the partitioning expression must be part of the unique key.
3. This can be done by setting PRIMARY KEY(sid_id, client_id). Then client_id can be used in the partitioning expression. Your queries might still be able to make use of the PK for performance. If you need to guarantee in the database that sid_id is unique, then you can create a unique index on sid_id in addition to the primary key. Because the table would already have a PK, this UK wouldn't affect your choice of columns for use in the partitioning expression.
If you can implement your app without using a unique key in the database, then go with Option 2. If you need a unique key, then go with Option 3.
Discussion of the relationship between partitioning keys, primary keys, and unique keys in the MySQL 5.1 Manual:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html#partitioning-limitations-partitioning-keys-unique-keys
I hope this helps.
[edited to fix URL]
Jon Stephens
MySQL Documentation Team @ Oracle
MySQL Dev Zone
MySQL Server Documentation
Oracle
Edited 1 time(s). Last edit at 09/05/2006 05:16PM by Jon Stephens.