MySQL Forums
Forum List  »  Partitioning

need help for partition on tables with foreign keys
Posted by: Johnson WIlson
Date: June 05, 2022 06:07PM

We have a few very huge tables in our dbs which need partitioningļ¼Œ with relationship between them.
such as transaction and transaction_log
transaction table records a serial of transactions from our application

CREATE TABLE TRANSACTION
(
TRANS_ID VARCHAR(10) NOT NULL,
CREATION_DATE DATETIME NOT NULL,
......................
PRIMARY KEY (TRANS_ID)
);


transaction_log table records all child events happened on the transaction since the transaction was created, like approval, decline, cancellation of the transaction
It can be joined to transaction table with TRANS_ID

CREATE TABLE TRANSACTION
(
TRANS_EVENT_ID VARCHAR(10) NOT NULL,
TRANS_ID VARCHAR(10) NOT NULL,
LAST_UPDATE_DATE DATETIME NOT NULL,
......................
PRIMARY KEY (TRANS_EVENT_ID )
);


The transaction table has TRANS_ID and creation_timestamp
THe transaction_event table has another ID and last_update of the event, both primary ID are random generated 10 digit string, and transaction_event table can be joined to transaction table using trans_ID

If we put partitioning on those tables based on date_time, it will cause problem when we drop and purge paritioning, for example, if we drop partition for 2020-12 partition on both transaction and event table, since transaction table is based
on creation timestamp, and event table is by last updated timestamp, the drop partitioing will fail because of the foreign key constrain.

We can't put partition based on ID either, because ID is randomly generated.

I cant find the best way for do this, i really appreciate if you could provide some suggestions.

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.