Thread: Foreign Keys

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

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    12

    Foreign Keys


    Hi all. I'm about to build what I consider to be my first "real" database application, ie. bigger than a couple of users and a few tables, and where I am designing the DB myself from scratch.

    I've got a question about foreign keys which I'm hoping someone can help with.

    I've got the following tables.

    Code:
    wms_users         wms_apps
    -------------------------------
    user_id           app_id
    username          app_name
    and I am going to create a third table, as follows:

    Code:
    wms_access
    ----------------
    app_id
    user_id
    Now, what I'm wondering is if this is a situation in which I would need to use foreign keys to ensure data integrity.

    For example if a user with user_id 15 has access to an application with app_id 22 and said user was deleted, I would not want to have the record kept in the wms_access table which referenced user_id 15.

    I know the desired results could easily be obtained via straight SQL, but I'd like to see why I should use foreign keys here instead.

    Can anyone give any advice here? I'm reading through the section on foreign keys in my PHP/PostgreSQL book but I'd like to get some opinions.

    Thanks in advance,
    Pablo
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    You can define the table wms_access this way:

    Code:
    create table wms_access
    (
        app_id int references wms_apps(app_id) on delete cascade,
        user_id int references wms_users(user_id) on delete cascade,
    )
    Of course, change the types to match the types of the referenced fields in the foreign table.

    The "on delete cascade" tells Postgres to delete any record in wms_access that matches the deleted record in the referenced table.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    12
    Foreign keys are a necessary constraint to enforce data integrity - why not use them, as your example is a clear case where they would be useful.

    Let the database take care of data integrity as much as possible. The alternative would be to have checks in the application code ... no fun for whoever is programming it.
    Casuistry Agnostic
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    Sheesh, I need to start reading the entire post.. Sorry for the irrelevent help.

    To be relevent:
    Data is often accessed by a multitude of applications. Meaning any data integrity checks not placed in the data structure itself and enforced by the dbms HAS to be in each and every application. If someone doesn't include that in their application, your data can become corrupted.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    12
    Thanks all for your posts. The information you provided was exactly what I was looking for. Got it set up and working perfectly on the first try.

    Thanks again!

    Pablo

IMN logo majestic logo threadwatch logo seochat tools logo