SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    make foreign key a key, really?

    Hey all. This is probably a dumb question, and one that I should already know but I'll ask it anyway.

    If I'm using an bigInt in a table as foreign key, and it is the primary
    key in another table, should I make it a "real" key?

    Could this really speed queries up? Maybe there's also some other benefits that I don't know about?

    tia,
    --ed

  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)
    yes, it will improve efficiency on joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup. If it is an actual key (it is unique) in the referencing table.

  4. #4
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by redemption
    Yup. If it is an actual key (it is unique) in the referencing table.
    Not sure which table you mean by "referencing" table.
    But I assume you mean the table that has the foreign key.

    Say I have two tables.

    Table1, and Table2.

    Table1
    -------------
    id | Table2_id | more fields ..


    Table2
    ------------
    id(primary key) | more fields...



    Table1.Table2_id is referencing Table2.id ..
    Table2.id is a primary key.

    Does Table1.Table2_id have to be unique?

    If a field that references another table has to be unique to
    be made a key, is there another way to optimize for joins?

    --ed

  5. #5
    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)
    > Does Table1.Table2_id have to be unique?

    oh my $deity, it had better not be, otherwise all you have is a zero-or-one-to-one relationship
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $deity?

    >> If a field that references another table has to be unique to
    be made a key, is there another way to optimize for joins?

    All keys have to be unique, otherwise they wouldn't actually be "keys" right?


    You're right about the referencing table being the table with the foreign key.
    Anyway, regarding the uniqueness of Table1.Table2_id, if you wanted to make it a key (like you did in the first place), it had to be unique right?

    If Table2_id can have multiple entries for say, "kenny", and "kenny" is of course in the primary key column of Table2, then there is no way you can make Table2_id a key of Table1 right (since you have 2 or more rows with "kenny")? Does that make sense?

  7. #7
    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)
    sorry, no, foreign keys do not have to be unique

    but i think the confusion is mysql's fault, which uses the word "key" interchangeably with "index"

    using the above examples...

    Table2.id is the primary key, therefore it gets a unique index

    Table1.Table2_id is a foreign key, which means squat to mysql (except innodb)

    however, Table1.Table2_id should get an index to improve join efficiency

    okay, here's where it gets ugly

    mysql lets you create an index using the word KEY

    so Table1.Table2_id is a foreign key which needs a KEY which should not be unique

    make more sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use INDEX in MySQL, so it doesn't make you confused.

    In any case, when people say "keys", its more often than not referring to "candidate keys", which are unique.

    Rudy, I know foreign keys do not have to be unique (in case you're replying to my post).

  9. #9
    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)
    yes, i was, i was specifically referring to this unfortunate juxtaposition:
    >> If a field that references another table has to be unique to
    be made a key, is there another way to optimize for joins?

    All keys have to be unique, otherwise they wouldn't actually be "keys" right?
    it seems to me that the question did seem to be about a foerign key, and that your answer suggested that it had to be unique

    i know that you know that foreign keys are typically NOT unique

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

  10. #10
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK just to make it clear what I'd meant.

    Regarding the question "If a field that references another table has to be unique to be made a key, is there another way to optimize for joins?"

    I took it to mean that: "If a field that references another table has to be unique to be made a candidate key..."

    And so I replied, yes all candidate keys have to be unique. The thought of a "key" as an "index" didn't occur to me.

    After all, it already is a foreign key, so I couldn't be commenting on it not being possible to be a foreign key, right? If coo_t2 actually meant to ask "If a field that references another table has to be unique to be made a foreign key, is there another way to optimize for joins?", then I'm terribly wrong, but I don't think that was what he meant.

  11. #11
    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)
    yeah, me too, i'm not sure what a lot of things really mean, eh


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

  12. #12
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I pretty much use phpMyAdmin any time I work with mysql.

    So would it be as simple as this? :

    1. Hit Table1 link on left.

    2. Hit "index" link for "Table1.table2_id" field.

    ??


    --ed

  13. #13
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup that seems to be correct.

  14. #14
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so 'primary key' is unique
    'key' is also unique
    but 'index' is not?

    and so I continue with making this thread more mess then it is say I have this table
    Code:
    Posts:
    id
    Users_id
    post
    
    Users:
    id
    name
    etc...
    Then id in user talbe is a primary key and Users_id in post table is foreign key ?

    what is foreign key then? is that just a name for the connection and column that is foreign key is often 'index' but not 'key' ?

    Just so confused...
    - website

  15. #15
    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)
    yes, foreign key is a special kind of logical relationship

    in mysql, key and index are synonymous (i.e. both are a physical index), and both can be unique or not, null or not

    a primary key is both logical and physical, and must be unique and not null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so should I declare Users_id 'key' or 'index' ? what would be the differance
    - website

  17. #17
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    >> Then id in user talbe is a primary key and Users_id in post table is foreign key ?

    That seems to be correct, assuming that it's purpose is as it seems, i.e. every Users_id in the Posts table must refer to an id in the Users table.

    >> so should I declare Users_id 'key' or 'index' ? what would be the differance

    In MySQL, no difference, since it treats KEY and INDEX as synonyms.

  18. #18
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    stupid mysql ...
    - website

  19. #19
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you have more than 1 key (as in candidate keys), you can (or rather, should) make 1 the PRIMARY KEY and specify the rest as UNIQUE.

    Making an INDEX is for physical tuning and has nothing to do with the conceptual schema (whereas PRIMARY KEYs and UNIQUE fields do). It just allows for some optimizations in queries involving the indexed field.

  20. #20
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok so PRIMARY KEY is just like UNIQUE except that it is the 'primary' key ?
    KEY == INDEX and speeds up querys (most often, at least with integer fields) by allowing mysql to search for eg every number starting with 1 but does not have to go down all the list?

    is this correct?
    - website

  21. #21
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    >> ok so PRIMARY KEY is just like UNIQUE except that it is the 'primary' key ?

    You use these constraints often for candidate keys in your schema, so yes. With the exception that UNIQUE allows for NULL values, while, of course, PRIMARY KEY does not.

    Consider the example table User(id, name, email). Suppose id is a key, and so is (name, email) - no user can have the same name AND email. Then you can do this:
    Code:
    CREATE TABLE (
      id INT PRIMARY KEY,
      name VARCHAR(30),
      email VARCHAR(50),
      UNIQUE (name, email)
    )
    Of course, you can also make (name, email) the primary key and id UNIQUE.

    >> KEY == INDEX and speeds up querys (most often, at least with integer fields) by allowing mysql to search for eg every number starting with 1 but does not have to go down all the list?

    How indexes work in MySQL -> http://www.mysql.com/doc/en/MySQL_indexes.html

  22. #22
    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)
    >> Of course, you can also make (name, email) the primary key and id UNIQUE.

    well, yes, you could, but you wouldn't

    i mean, if (name,email) is the primary key, then what the heck do you need id for?

    if, as in so many cases where you have a column called id, it happens to be an auto_increment, then you don't have to bother declaring it unique, because an auto_increment is unique by its very nature...

    when you do use an auto_increment id, it does typically have the role of primary key, and in that case it's a surrogate key

    thoroughly confused yet?

  23. #23
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup I truly appreciate that fact, of adding in an extra column just so to get an auto-incrementing field. I make it a point to keep these to a minimum when possible. I've noticed that web-based apps often have an auto-incrementing field which I attribute to the need for easier query strings (think http://www.sitepointforums.com/showthread.php?posttitle=make%20foreign%20key%20a%20key,%20really&postforum=databases ), and also to tutorials and articles that seem to encourage adding an auto-increment field just to get a primary key.

    However, like when it is the user ID of the user (which was what I had actually meant by that example, though it was most unfortunately named "id", leading to it sound like an auto_increment column ), which you actually needed to store, or when id represents the identification card no. of a user, then I guess you probably needed it at first.

  24. #24
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but isn't required to have primary key in a table?
    - website

  25. #25
    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)
    no, a table is not required to have a primary key

    (a relation is, but that's an entirely different thread, and i don't want to go there, because it will eventually take us to dbdebunk again)

    you only need a primary key when the database enforces foreign key relationships (which mysql does only for innodb tables) and there is at least one foreign key that references the primary


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
  •