-1

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?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Rajat Aggarwal
  • 392
  • 3
  • 16
  • 1
    Columns participating in foreign keys must be indexed in both tables, otherwise you get an error message when you try to create the FK. And, yeah, mysql does not have a magic bullet either, it has to look the related value up and remove the corresponding row. – Shadow Dec 09 '21 at 18:47
  • 2
    The tag doesn't cover how MySQL implements their functionality. – jarlh Dec 09 '21 at 18:52

1 Answers1

-1

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.

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
  • 2
    In the case of MySQL, it's not possible for a foreign key column not to be indexed. Defining a foreign key creates an index automatically if one doesn't exist. That wasn't always true; but the versions of MySQL that once required you to manually create indexes for FK's have been end-of-life for many years. – Bill Karwin Dec 09 '21 at 18:50