MySQL Forums
Forum List  »  Partitioning

Re: Partition With out use Primary Key
Posted by: Jonathan Stephens
Date: August 22, 2006 03:10AM

"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:

I hope this helps.

[edited to fix URL]

Jon Stephens
MySQL Documentation Team @ Oracle
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation

Edited 1 time(s). Last edit at 09/05/2006 05:16PM by Jon Stephens.

Options: ReplyQuote

Written By
Re: Partition With out use Primary Key
August 22, 2006 03:10AM

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.