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

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10

    Foreign Key newbie questions


    Hi. I'm just trying to wrap my head around foreign keys.

    1) Must referenced columns always be primary or at least unique or at least a key? Can it just be any old column?

    I guess I'm confused because I always thought of a foreign key as any column that you can join on. But that doesn't sound like the case. That column must be UNIQUE?

    2) Do both ON DELETE and ON UPDATE always have to be set to something or can you opt for nothing to happen in either case? If you can opt for nothing is there any other reason you would bother explicitly declaring a foreign key?


    3) Is the primary reason we want to explicitly declare foreign keys because of the ON DELETE / ON UPDATE functionality? Otherwise, what's the point?
    Last edited by dano2; June 20th, 2013 at 06:23 PM.
    Thanks,
    Dan

    Operating system ubuntu 12.04
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    1) yes

    2) you can opt for NO ACTION

    3) the point is data integrity -- you cannot add a row to the child table if the parent key does not exist
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10
    Originally Posted by r937
    1) yes

    2) you can opt for NO ACTION

    3) the point is data integrity -- you cannot add a row to the child table if the parent key does not exist
    Sorry, I edited my question a little, but I gather that the referenced column must be, at minimum, an explicitly declared unique key.

    So, even if NO ACTION is selected, the point is that if I tried to add a row referencing a parent key value that doesn't exist, mysql will prevent me from adding it? And that ENFORCES referential integrity which is the goal.
    Thanks,
    Dan

    Operating system ubuntu 12.04
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by dano2
    Sorry, I edited my question a little, but I gather that the referenced column must be, at minimum, an explicitly declared unique key.
    Not sure what the "yes" by r937 referred to, but the referenced columns do not have to be unique. They only have to be indexed in a certain way (a way which allows fast lookups).

    The manual explains this very well.

    Maybe this is different from the SQL standard or relational algebra, but it's the same with other mainstream database systems like PostgreSQL or Oracle DB.



    Originally Posted by dano2
    So, even if NO ACTION is selected, the point is that if I tried to add a row referencing a parent key value that doesn't exist, mysql will prevent me from adding it? And that ENFORCES referential integrity which is the goal.
    That's true. In reality, foreign keys are mostly used to enforce valid references to the primary key of a parent table.

    Let's say you have an online shop. Then you wanna make sure that your orders only contain actual products and not gargabe data caused by some bug in the application.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Jacques1
    Not sure what the "yes" by r937 referred to...
    if you look a little bit harder, it is in response to the question (there was only one) in the previous post



    Originally Posted by Jacques1
    the referenced columns do not have to be unique.
    this is such a bastardization of the standard that it should never be mentioned



    Originally Posted by Jacques1
    Maybe this is different from the SQL standard or relational algebra, but it's the same with other mainstream database systems like PostgreSQL or Oracle DB.
    you're saying that postgresql and oracle do not require a referenced key to be unique? my estimation of those two systems just slipped a couple notches
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10
    Originally Posted by Jacques1
    Not sure what the "yes" by r937 referred to, but the referenced columns do not have to be unique. They only have to be indexed in a certain way (a way which allows fast lookups).
    Interesting. "InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL". I'm reading that as, it can be done but best practice is to keep the parent key unique.

    Originally Posted by Jacques1
    ...bug in the application.
    I guess I can appreciate the extra layer of reinforcement foreign keys provide, but it almost seems too redundant. It seems they only serve to compensate for a poorly written app. All of the foreign key actions (including the prevention of adding children without parents) have to be addressed at the app level anyway, whether a foreign key exists or not, even if only in the form of handling those foreign key violation exceptions.

    So, since it doesn't take any burden off the app developer and has a cost in the form of creation/management/frustration time, I could see where the argument that they are more trouble than they're worth comes from.

    Do you know a quick way in phpmyadmin to see if a key is foreign. The table's "Structure" pages, just indicates that it's a key, no mention of 'foreign'?
    Thanks,
    Dan

    Operating system ubuntu 12.04
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by dano2
    It seems they only serve to compensate for a poorly written app.
    i don't buy this argument at all

    "best practice" involves letting the database control the relationship and then issuing (user friendly) error statements from the app when a foreign key constraint is violated

    doing the same thing in the app requires additional queries (e.g. to see if the pk exists when inserting a row with a foreign key), which effectively doubles the processing load on the database engine

    a competent application developer will (a) know how to trap database error messages and interpret them, and (b) never dream of not letting the database handle this type of integrity

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

IMN logo majestic logo threadwatch logo seochat tools logo