Unable to create partition on Mariadb on this child table where Foreign Key Exists?

Question

Unable to create partition this child table "city" with parent table "country" It is showing below error "Foreign Key Not Supported". Without foreign key how relation maintain

ForeignKey not supported by partition] [2] https://i.sstatic.net/WIlQR.png [1]: https://i.sstatic.net/CpjOv.png

Answer 1

Partitioning tables with FOREIGN KEY is not supported in MariaDB.

According to the MariaDB documentation:

• A partitioned table cannot contain, or be referenced by, foreign keys.
• All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
• Each table can contain a maximum of 8192 partitions (from MariaDB 10.0.4). In MariaDB 5.5 and until 10.0.3, the limit was 1024.

As an alternative to Foreign Keys you could use Triggers to update the partitioned table when a parent table changes.

Recent versions of PostgreSQL do support Foreign Keys for Partitioned Tables. If that is important to you, you might want to have a look there.