Thread: Foreign Keys

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    6
    Rep Power
    0

    Foreign Keys


    Hi, Im a beginner so please forgive what may have a simple answer. Hope i explain this OK. I'm still at the theory stage. If I have two tables. Table 1, first column has the id, second column has a list of names, third has there ages. Second Table - first column has id, second column has the foreign key linked to table 1 id's. rest of columns have other data. My question is, does a column in the second table have to have to also contain the name matching the FK from the first table. From what ive read Im thinking it doesn't because this is duplicating data which i gather the idea is to avoid this. This doesn't seem very clear in the books I've read. I'm think that when I use SQL to query this Im referencing the FK to get relevant data.
    Regards Robert
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by robcpettit
    does a column in the second table have to have to also contain the name matching the FK from the first table.
    nope
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    6
    Rep Power
    0
    Thank-you
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    10
    Rep Power
    0

    Best Practice


    While the column name does not have to match obviously the value does for the join. I do recommend having them be the same name so that your design is easier for outsiders to understand.

    I've found that it is good practice to name your primary keys <TABLENAME>_KEY or something similar depending on your company's naming conventions. You then use the same name in the other table as the foreign key.

    Some query editors will auto-match which can cause a problem if (and I see this all the time) all of your primary keys for tables are named "PK" or "ID." Then you can accidentally have a join on primary key = primary key (OH NO!)

IMN logo majestic logo threadwatch logo seochat tools logo