Re: Technical Query on Mysql--InnoDB storage engine currently doesn’t support Partitioning on Table which have Foreign key references.

[Rick James]

Sorry, you have to choose between FKs and PARTITIONs.

I would pick DATE-RANGE PARTITIONs, then move the FK code into an application layer.

PARTITIONs: 11 -- 10 for the months, 1 for "future" data. More details:
http://mysql.rjweb.org/doc.php/partitionmaint

The data layer would receive requests for inserts/etc and do the FK-like checks before actually doing INSERTs/etc. This "layer" gives you more control over FK actions, plus it lets you prevent users interact directly with MySQL -- I believe this is good for the integrity of the system.

Archiving -- If you have a new enough version of MySQL, one with "transportable tablespaces", then the 'old' partition can turned into a separate table. This would be an efficient way to migrate the data to an 'archive'. Otherwise, you would need to SELECT the old rows and INSERT them in whatever you have for an "archive".

If you choose to avoid PARTITIONs, then I can help you with efficiently transferring and deleting 'chunks' of rows to an 'archive. Let's see to PRIMARY KEY (or other UNIQUE key) for the table.
See http://mysql.rjweb.org/doc.php/deletebig