SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    too many foreign keys

    Hi guys

    Could there be a problem in the design of a database schema which has a lot of foreign keys?
    Also, if I define a foreign key between column A to column B, does the database index column A?

    thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    question 1: define "a lot"

    question 2: no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) My question should be: Is there a disadvantage to using foreign keys (except for the storage they take)? Can they hurt performance?

    2) If I set a column in a table as a primary key, does an index automatically gets generated for that column, or do I have to create an index for it explicitly?

    thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1) in myisam tables, there is neither advantage nor disadvantage, in innodb tables, there is only advantage, no disadvantage (well, except for a few cycles, but you want foreign keys turned on, otherwise you might as well use myisam tables)

    2) primary key automatically creates a unique index (both myisam and innodb)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "primary key automatically creates a unique index (both myisam and innodb)"

    do they also automatically create a regular index (for improving performance) ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a unique index is a regular index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    1) in myisam tables, there is neither advantage nor disadvantage
    Well that's because myisam doesn't support foreign key

    in innodb tables, there is only advantage, no disadvantage (well, except for a few cycles, but you want foreign keys turned on, otherwise you might as well use myisam tables)
    Well there's a slowdown if you do lots of inserts, or if you update the column with the foreign key referencing another table. In this case, the database has to do a hidden SELECT everytime to make sure the foreign key is valid. That's why when populating a large table (ie restoring from a dump) with some foreign key constraints it can be interesting to temporary turn of the foreign key verification to speed up things.


Bookmarks

Posting Permissions

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