> 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.
