Adding FK to a newly attached partition slow

Question

We have a partitioned table from which we attach and detach partitions for archiving purposes. When attempting to add a foreign key on the partitioned table, the process is taking a long time (up to 20 minutes) which is causing many problems.

We have discussed using a NOT VALID option, but unfortunately this is not an option for partition tables, so just wanted to see if there were any options to add a foreign key to a partition

Answer 1

I recommend that you do not create a foreign key constraint on the partitioned table. If you do that, attaching partitions will always be slow, because the constraint has to be validated for all rows in the new partition.

My recommendation is to create the foreign key constraint on the individual partitions. You don't lose anything that way, and your problem vanishes, because you can create NOT VALID foreign key constraints on the partitions.