CODEWITHSUNDEEP
sqlmysqlindexingunique
David Carney
David Carney

Reputation: 2210

How do I add a unique index on a field in a partitioned MySQL DB?

I have a table, foo, that is partitioned by 'created_at' and has its primary key as (id,created_at). I.e.:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `bar_id` int(11) DEFAULT NULL,
   ...

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
(PARTITION p0 VALUES LESS THAN (733712) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (733773) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (733832) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (733893) ENGINE = InnoDB,
...
)

How do I create a unique index such that bar_id is unique across all partitions? If I try something like:

CREATE UNIQUE INDEX some_index USING BTREE ON foo (bar_id);

I receive the error:

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

But, if I include the partitioning function (id, created_at) when building the index then I end up with an index that does not guarantee that bar_id is unique.

UPDATE

I should have mentioned that I already have a primary key on the table: 

PRIMARY KEY (`id`,`created_at`)

Also, bar_id can be NULL.

Upvotes: 6

Views: 5811

Answers (3)

azzaxp
azzaxp

Reputation: 717

The error message itself explains the problem. Please, read the manual, http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations-partitioning-keys-unique-keys.html

"The rule is : All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. In simple words, every unique key on the table must use every column in the table's partitioning expression."

Upvotes: 2

The Scrum Meister
The Scrum Meister

Reputation: 30111

This can be done via a secondary table to store the unique bar_id, and a before-insert trigger.

Upvotes: 0

mluebke
mluebke

Reputation: 8819

Create a Primary Key.

From the MySQL docs on partitioning: any column used for a partitioning expression must be part of the table's primary key (if it has one) or (first) unique key (if it has a unique key but no primary key). Otherwise, you'll see the error message, "A PRIMARY KEY needs to include all fields in the partition function".

If a table has no primary key, but does have one or more unique keys, then any columns used in the partitioning expression must be part of the (first) unique key.

Upvotes: 0

Related Questions