CODEWITHSUNDEEP
mysqldatabasepartitioningdatabase-partitioning
user2274074
user2274074

Reputation: 1087

Mysql partition - How to do list partitioning of a table that contains unique column?

I am doing mysql list partitioning. my table data is as below

----------------------------------------
id | unique_token | city | student_name |
----------------------------------------
1  | xyz          |mumbai| sanjay       |
-----------------------------------------
2  | abc          |mumbai| vijay        |
----------------------------------------
3  | def          | pune | ajay         |
----------------------------------------

In the above table unique_token column has a unique key and i want to do list partitioning with city column. As per mysql documentation every partition column must be part of every unique key of a table and hence in order to do list partitioning with city column i have to create new unique key as unique_key(unique_token,city).

Now the issue is that unique_token column should be unique and if i insert two rows in the table as ('xyz','banglore') and ('xyz','pune') then these rows will be inserted into the table but then unique_token column won't be unique at all.

I want to know how to do list partitioning on this table without having duplicate data in unique_token column??

Upvotes: 0

Views: 662

Answers (2)

Rick James
Rick James

Reputation: 142453

There are limitations in MySQL's PARTITION implementation. In particular, no FOREIGN KEYs and no UNIQUE keys unless they happen to include the "partition key". These limitation exist because of the unacceptable cost of implementing them. This, in turn, is caused by each partition being essentially a separate 'table', with its own indexes. There is no "index" that spans the entire set of partitions. Such a 'global index' would make FKs and UNIQUE keys viable and efficient. This may come in version 5.8.

Meanwhile, let me change your question from "How to do LIST partitioning..." to "Why do LIST partitioning at all?". I know of no utility -- not performance, not convenience, not anything else, for PARTITION BY LIST. If you have a reason for wanting to do it, please explain. I would be happy to change my rather negative attitude toward partitioning. (I know of only 4 use cases for PARTITION BY RANGE, but that is another topic.)

Upvotes: 2

Suresh Alathur
Suresh Alathur

Reputation: 93

Better to give composite primary key for (unique_token and city) columns

alter table table_name add constraint constraint_name primary key(unique_token and city).

Upvotes: 0

Related Questions