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 <greenl...@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.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
