Comments - System-Versioned Tables

6 years, 8 months ago Karsten Budde

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

 
6 years, 7 months ago Sergei Golubchik

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);
 
5 years, 12 months ago Friedemann Schmidt

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);
 
5 years, 12 months ago Ralf Gebhardt

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.

 
5 years, 12 months ago Friedemann Schmidt

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?

 
5 years, 12 months ago Friedemann Schmidt

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

This is a pity.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.