Comments - System-Versioned Tables

Hello,

you discribe, how I can drop a historical partition. Can I also add a historical partition? How is the syntax to do this?

Particulary with regard to historical partitions by time it is useful to add new historical partitions.

Kind regards, K. Budde

to drop a historical partition use DROP PARTITION. To add a historical partition use ADD PARTITION. Like

alter table t1 drop partition p_hist1;

alter table t1 add partition (partition p_hist3 history);

You have described how to create partitions when creating the table. Is it also possible to add it later? I tried to add a partition, but unfortunately I couldn't do that: Both failed:

alter table t1 add partition 
             (partition p_hist history, 
              partition p_cur current);
alter table t1 add partition by SYSTEM_TIME
             (partition p_hist history, 
              partition p_cur current);

The syntax for adding partitioning to a not partitioned table would be

alter table t1 partition by SYSTEM_TIME (partition p_hist history, partition p_cur current);

"add" only can be used for already partitioned tables.

Thank you. For one Table it worked. But for another table, this error occurred: Cannot delete or update a parent row: a foreign key constraint fails

What have I done wrong?

OK, I found in the documentation of partitioning limitations: A partitioned table cannot contain, or be referenced by, foreign keys

This is a pity.

See https://jira.mariadb.org/browse/MDEV-12483