Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010

    Unanswered: Foreign keys - what's the overhead?

    Hi everyone!
    Got a question about foreign keys. As for me, I think that they are really convenient thing. But I've read some opinions that they add overhead and it's not recommended to use them on a highload projects. I googled a bit about this topic, but found nothing.

    What do you think? Is this true?

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by tedd View Post
    H But I've read some opinions that they add overhead and it's not recommended to use them on a highload projects.
    you've been reading the wrong stuff

    the "overhead" of foreign keys is inconsequential

    the benefit of foreign keys is data integrity

    you decide | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    San Sebastian, Spain
    The overhead is performance of inserts/updates and delete operations on the tables. Every insert needs to cross check with the other tables that the key exists. So for a table with only 1 foreign key the insert will cause a check in the foreign key table before applying the INSERT statement.

    For large numbers of inserts, deletes and updates this will become an issue, but, for OLTP type transactions where a single insert it taking place then indeed the overhead will not be significant and would justify including the foreign key constraints.

    On warehouse type databases where large volumes are loaded periodically, then using foreign key constraints can be a hindrance due to the performance overhead. A better approach here is to load the data into staging tables (identical setup to the primary table) and load the information in here. None of the tables will have foreign key constraints. However, if you generate an SQL statement that verifies the integrity of the data will a) verify the integrity of the data and b) will allow data cleanup before applying into the primary table. It should be noted that warehouse type databases perform inserts and rarely perform updates or deletes so the data remains static.

    Hope this helps?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

  4. #4
    Join Date
    Nov 2010
    Rudy, Ronan, thanks for answers! Now the benefits and limitations of foreign keys are obvious to me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts