On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, <laurenz.a...@cybertec.at> 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.