On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, <laurenz.albe@xxxxxxxxxxx> wrote:
On Fri, 2024-02-23 at 02:05 +0530, yudhi s
> 2)Should we be creating composite indexes on each foreign key for table2 and table3, because
> any update or delete on parent is going to take lock on all child tables?
Every foreign key needs its own index. A composite index is only appropriate if the foreign
key spans multiple columns.
From the DDL which OP posted it's using composite foreign key thus a composite index would be needed.
However, if someone doesn't delete or update the parent table PK , is it still advisable to have all the FK indexed? Like in general I think transaction id should not get updated in a normal scenario unless some special case.
