I have two options in my mind to solve this problem;
If there is a foreign key relationship in one of the tables we want to change colocation group, error out;
This is easier to implement. We already implemented functions to find out whether a foreign key is defined over a table. However if user changes colocation group of both referenced and referencing tables together, this method will error out unnecessarily.
If there is a foreign key relationship in one of the tables we want to change colocation group, first scan other tables we want to change colocation group. If that foreign key is defined between two tables we change colocation group, there is no need to error out.
This solution is more involved but it is still moderately easy to implement. We just need to iterate over some loops. This solution also fixes the problem in the previous option.
If a foreign key is defined to or from a table, we should not allow changing colocation group of this table. Currently we do not have such check.
To replicate the problem;
I have two options in my mind to solve this problem;
If there is a foreign key relationship in one of the tables we want to change colocation group, error out; This is easier to implement. We already implemented functions to find out whether a foreign key is defined over a table. However if user changes colocation group of both referenced and referencing tables together, this method will error out unnecessarily.
If there is a foreign key relationship in one of the tables we want to change colocation group, first scan other tables we want to change colocation group. If that foreign key is defined between two tables we change colocation group, there is no need to error out. This solution is more involved but it is still moderately easy to implement. We just need to iterate over some loops. This solution also fixes the problem in the previous option.