Restrictions on unique constraints of Mysql partition tables

I believe you have some knowledge about Mysql partition tables. The following describes the limitations of Mysql partition tables on unique constraints. If you are interested in this, take a look.

Mysql Partition Table restrictions on Partitioning Keys, Primary Keys, and Unique Keys

In Mysql 5.1, the unique constraints of a partition table are clearly defined. Each unique constraint must contain the partition key of the partition table and the primary key constraint ).
This sentence may not be easy to understand. Let's do a few experiments:

 
 

  
  
CREATE TABLE t1     
  
  
(      id INT NOT NULL,  
  
  
       uid INT NOT NULL,   
  
  
       PRIMARY KEY (id)  
  
  
)  
  
  
PARTITION BY RANGE (id)     
  
  
(PARTITION p0 VALUES LESS THAN(5) ENGINE = INNODB,   
  
  
 PARTITION p1 VALUES LESS THAN(10) ENGINE = INNODB);   
  
  
CREATE TABLE t1     
  
  
(      id INT NOT NULL,      
  
  
       uid INT NOT NULL,  
  
  
       PRIMARY KEY (id)  
  
  
)  
  
  
PARTITION BY RANGE (id)     
  
  
(PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp',   
  
  
PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp');   
  
  
 
  
  
mysql> CREATE TABLE t1    
  
  
     -> (      id INT NOT NULL,      
  
  
     ->        uid INT NOT NULL,   
  
  
     ->        PRIMARY KEY (id),  
  
  
     ->        UNIQUE KEY (uid)   
  
  
     -> )  
  
  
     -> PARTITION BY RANGE (id)   
  
  
     -> (PARTITION p0 VALUES LESS THAN(5),  
  
  
     ->  PARTITION p1 VALUES LESS THAN(10)   
  
  
     -> );ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function  
  
  
 
 
 

In-depth discussion on MySQL Lock Mechanism

Detailed description of MySQL Data Table types

Enumeration in the MySQL Field

Add and delete fields in MySQL

MySQL Field Type