SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: foreign key(s)

  1. #1
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    foreign key(s)

    The quick question would be: yes/no/perhaps ?

    The long version:
    Should foreign keys be used and if so when?
    or is it more a 'it-depends-on-...' thingie?

    For what i've read about it so far it's more of a consistency thing and not a must have and it creates some overhead and that's why bigger companies (with big databases) put the 'checking' into the application, not the db.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by frezno View Post
    ... and that's why bigger companies (with big databases) put the 'checking' into the application, not the db.
    i don't know where you got the information that led to this conclusion, but there is a word for this type of conclusion, and that word is wrong

    yes, you should always declare foreign keys (except for myisam tables, which you should use only rarely anyway)

    foreign keys are definitely a "must have" because they ensure data integrity

    putting the checking into the application means (1) more overhead in the application, and (2) a reliance on the application for all data entry, i.e. you cannot dare to load data into the database any other way because there are no data integrity controls
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't know where you got the information that led to this conclusion, but there is a word for this type of conclusion, and that word is wrong
    i got it right from the horses mouth aka MySQL manual:

    Do keep in mind that these benefits come at the cost of additional overhead for the database server to perform the necessary checks. Additional checking by the server affects performance, which for some applications may be sufficiently undesirable as to be avoided if possible. (Some major commercial applications have coded the foreign key logic at the application level for this reason.)
    But that's why i'm asking the real expert here

    Quote Originally Posted by r937 View Post
    yes, you should always declare foreign keys
    got it, thank you

    so now i just have to check why some foreign keys can be set and some not (at my tables)
    Pretty likely i'll be back
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the mysql manual, as always, is a good source of information

    note that it says "some major commercial applications..." whereas you said " bigger companies (with big databases)..."

    significant difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •