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.lefr...@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.gh...@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
_______________________________________________
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
