Restrictions on primary key constraints in Mysql partition tables

Mysql partition tables have certain restrictions on primary key constraints. The following describes the restrictions on primary key constraints for Mysql partition tables in Mysql for your reference.

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 Mysql 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  
 
 

 

MySQL environment variable configuration instructions

Example of defining MySQL transactions

Mysql Stored Procedure FAQs

Example of creating a MySQL Stored Procedure

Use of mysql ifnull Functions