I have a tableA with primary key which acts for a foreign key for tableB.

How does sql delete rows from tableB if a row is deleted form tableA, assuming ON DELETE CASCADE is on.

Does SQL performs full-table scan for tableB? And what if foreign column is indexed?

CodePudding user response：

The DB will perform a full-table scan on B unless it doesn't need to because the column is indexed. This is why you should always have your FKs indexed.

Edit: I see from @BillKarwin's comment that MySQL will not allow you to have an FK without the target column being indexed. Oracle, my area of expertise, will allow it, and will as noted above do a full scan of the foreign table looking for rows to delete, which is of course very slow.