Oops.  PRIMARY KEY should be just KEY on that second example with RANGE COLUMNS.  :)

Sent from my iPhone

On Mar 12, 2016, at 11:01 AM, Justin Swanhart <greenlion@gmail.com> wrote:

You can use no primary key, and just put a regular key (not unique key) on the auto_increment column.  You must make sure that manual insertions don't create a duplicate if value.  

CREATE TABLE new (
         id INT NOT NULL AUTO_INCREMENT,
         name VARCHAR(50),
        added DATE,
KEY (id),
     )     
    PARTITION BY RANGE( TO_DAYS(added) ) 
  ( PARTITION p0 VALUES LESS THAN (1990),
   PARTITION p1 VALUES LESS THAN (2000),
   PARTITION p2 VALUES LESS THAN MAXVALUE  
	 ) ;


Look at RANGE COLUMNS for a more natural way to partition by date:
CREATE TABLE new (
         id INT NOT NULL AUTO_INCREMENT,
         name VARCHAR(50),
        added DATE,
         PRIMARY KEY (id),
     )     
    PARTITION BY RANGE COLUMNS (added) 
  ( PARTITION p0 VALUES LESS THAN ('1990-01-01'),
....
   PARTITION pmax VALUES LESS THAN MAXVALUE  
	 ) ;
Sent from my iPhone

On Mar 12, 2016, at 5:16 AM, Guillaume Lefranc <guillaume.lefranc@mariadb.com> wrote:

The only solution is to make (id, added) a PK, because the partitioning column must always belong to unique key, if there is such.
In your case I would change added type to DATETIME to avoid collision.

Regards

On Sat, Mar 12, 2016 at 2:10 PM Ghazi Btissam <btissam.ghazi@gmail.com> wrote:

Hi,

1- I have a table with a unique key which is calculated by the AUTO_INCREMENT feature, and I need to range partition it using a date field , is it possible and/or how to do it?

2- Is there a way to implement sequences for the primary key (like it exists in Oracle) or the AUTO_INCREMENT is the only one in MariaDb?

For example:

 CREATE TABLE new (
 
         id INT NOT NULL AUTO_INCREMENT,
 
         name VARCHAR(50),
 
        added DATE,
 
         PRIMARY KEY (id),
 
     )     
    PARTITION BY RANGE( TO_DAYS(added) ) 
  ( PARTITION p0 VALUES LESS THAN (1990),
 
   PARTITION p1 VALUES LESS THAN (2000),
 
   PARTITION p2 VALUES LESS THAN MAXVALUE  
	 ) ;

Thanks

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp
--
Guillaume Lefranc
Remote DBA Services Manager
MariaDB Corporation
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp