Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    18

    Unanswered: Understanding how Foreign Keys work

    Hello All!

    I'm very new to DB programming and want to do things correctly. So, I've decided to study the correct way to build a relational database.

    I've studied some stuff on line so I understand the concept of and reason for nomalized forms 1, 2 and 3.

    But I'm having a problem understanding how foreign keys work in child tables - wether they dynamically link data to/from parent tables, or if they're just static indexes to those tables.

    I'm eager to study this whole process, but I'm on a very thin dime in the Philippines (I'm an American expat) and would like to request from anyone a link to a good site which would explain the functionality of foreign keys IN A VERY SIMPLE AND UNDERSTANDABLE WAY for a neophyte such as my self.

    Would greatly appreciate any and all guidance on this topic.

    Thanks to all in advance!

    Expatriate

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    foreign keys in a nutshell

    - parent table contains primary key
    - child table contains foreign key
    - the "link" is by value
    - dbms ensures that every value inserted into foreign key already exists in primary key
    - dbms also ensures primary key updates or deletes are handled according to specified rules (restrict, set null, and cascade are common options)

    that's pretty much all there is to it

    any questions?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2006
    Posts
    18

    Boy, do I have questions!

    I understand what is suppose to happen (as you've outlined) conceptually, but I don't understand HOW it works.

    Here's a brief overview of what I'm doing:

    I've got a "Room Categories" table. In it, there are 3 fields:

    1) "categoryID" tinyint, autoincrement (PK)
    2) "roomCategory" varchar (15) - a room category would be "standard, deluxe, family, or penthouse"
    3) "roomDescription" varchar (255)

    Note: this table will only contain 4 rows; each row will have one of 4 possible "categoryID" values: 1, 2, 3 or 4.

    Then, I've got a "Room Location" table which holds room numbers and room locations (i.e. 1, 2, or 3 [floor]).

    This table has 4 fields:

    1) "locationID" tinyint, autoincrement (PK)
    2) "roomNumber" varchar (2) -- room numbers are alpha/numeric
    3) "roomLocation" tinyint
    4) "categoryID_FK" (foreign key from the Room Categories table).

    Note: this table will contain 17 rows (maybe more if the hotel adds rooms) but as of now, there are only 17 different rooms (in 4 specific room categories).

    >>dbms ensures that every value inserted into foreign key already exists in primary key<<


    That's my core question: How does the "categoryID_FK" field "know" what "categoryID" values belong to which room numbers and room locations?

    The only way I can think this can happen is if I manually input the correct categoryID values into the proper rows of the categoryID_FK field (room location table).

    That doesn't sound right...or is it? Have I missed something?

    REALLY appreciate your guidance and willingness to help.

    Expatriate
    Last edited by expatriate; 04-04-06 at 13:10.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by expatriate
    The only way I can think this can happen is if I manually input the correct categoryID values into the proper rows of the categoryID_FK field (room location table).
    that's exactly correct, sir

    look at it this way: suppose you have everything all set up properly, with primary/foreign keys, and all the rows that are in there now are referenced properly, so that each room is correctly associated via the foreign key to the proper category

    okay, now here's the "Gedankenexperiment" (as A.Einstein used to call them)

    suppose you now want to add a new room to the database

    how is the database supposed to know which category it belongs to

    you gotta tell it!



    where the relational integrity part comes into play is if you told the database "this room belongs to category 5" -- the database would tell you "no way, i'm not adding that room because there is no category 5"

    make sense now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2006
    Posts
    18

    Ahhhhhh

    r937 -

    Got it. The whisps of confusion are now gone!

    Thanks so much for your help; now I can get on with the job instead of losing sleep over "what to do"!.

    ONE LEAD ON QUESTION:

    In my scenario, I'm trying to do this to normalized form 1, but the Room Location table WILL have multiple 1's, 2's, or 3's in the room location field as, of course, there will be multiple room categories that share the same floor.

    So...where does one draw the line when it comes to trying NOT to duplicate data in a table?

    Expatriate

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    multiple room categories that share the same floor?

    no

    multiple rooms that share the same category, multiple rooms that share the same floor, but not multiple room categories that share the same floor

    room location (floor) is actually a foreign key, isn't it

    however, if you use the value 1 to represent floor 1, and the value 2 to represent floor 2, etc. then you don't really need a room location (floor) table, the foreign key is actually a natural key but it's a "virtual" foreign key at the same time

    get it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2006
    Posts
    18

    Thanks!

    r937 -

    Sorry for the long delay in my final response. You've been a great help to me in understanding foreign keys - "natural keys" and "virtual foreign keys" have got my head swimming though...but that's okay: one step at a time!

    Appreciate all the time you've taken to assist me.

    I've also taken a look at your website and will be using it for reference as well.

    Thanks again!

    Expatriate

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're welcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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