Index | Architecture and Data Blog

Create an Index for all FK Columns in the database

Generating indexes for all foreign key constraints in the database

Most of the time I have seen database foreign key constraints on tables without indexes on those columns. Lets say the application is trying to delete a row from the CUSTOMER table

DELETE FROM CUSTOMER WHERE CUSTOMERID = 1000;

When the database goes about deleting the customerId of 1000, if there are foreign key constraints defined on customerId, then the database is going to try to find if the customerId of 1000 is used in any of those tables. Lets say ORDER table has the customerId column, the database is going to issue

Create a Index for every Foreign Key constraint created

Foreign key constraints need indexes for efficient enforcement

When creating a Foreign Key constraint on the database as shown below

ALTER TABLE BOOK ADD
    (CONSTRAINT FK_BOOK_AUTHOR FOREIGN KEY (AUTHORID)
     REFERENCES AUTHOR)
/

In the above example we are telling the database to check if the BOOK.AUTHORID is a valid value in the Author.AuthorID. When the Author table is being changed, the database does data verification on the BOOK table using SELECT against the BOOK table for the AUTHORID some thing like this

Create an Index for all FK Columns in the database

Generating indexes for all foreign key constraints in the database

Create a Index for every Foreign Key constraint created

Foreign key constraints need indexes for efficient enforcement

FEATURED TAGS