Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2007
    Posts
    36

    Unanswered: foreign key has null value

    I've two tables 1. Product_episode 2. order_episode

    there is a key product_episode_id (Nullable) in Order_episode which is a foriegn key to the same key in product_episode.

    The key product_episode_id is the primary key in product_episode.


    I observed there are few nulls for p_e_id in the table order_episode.
    since there is primary & foreign key relation ship, why the system allowd it?

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    product_episode_id (Nullable)
    Hi,
    in foreign column there are allowed to be only the values from parent primary key values and nulls! If you would not like to have null values in foreign column then you should define foreign key column as NOT NULL.
    Regards,
    Grofaty

  3. #3
    Join Date
    Nov 2007
    Posts
    36
    When there are NULLs in the table, how the matching between foreign key and primary key values being satisfied?

    How can we understand the records with NULL values with the other table?

    Thanks,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a NULL foreign key means that that particular row is not related

    example: players belong to a team, but no more than one team at a time, so you can have the team_id as a foreign key in the players table, but when a team releases a player, he is no longer with a team, so you set the foreign key to NULL, but you don't delete the player, and later, when he joins a new team, you can update the foreign key and change the NULL to his new team

    simple, eh

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

  5. #5
    Join Date
    Oct 2007
    Posts
    55
    Provided Answers: 3
    Ani,
    You have to tell us how the records are related. The relationship between the tables states that the Foreign Key is NULLable and apparently your application sometimes inserts a NULL value for the foreign key. If you do not wish for this to happen, then you should redefine the foreign key to not allow nulls, is it maybe a delete rule on the foreign key definition that set the child table to null?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Short summary of Rudy's example: You can use nullable columns in the dependent table/columns allow you to represent orphan rows.

    There is no issue with "relationship" because SQL defines relationships based on values and NULL is not a value; it is a marker.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by r937
    a NULL foreign key means that that particular row is not related

    example: players belong to a team, but no more than one team at a time, so you can have the team_id as a foreign key in the players table, but when a team releases a player, he is no longer with a team, so you set the foreign key to NULL, but you don't delete the player, and later, when he joins a new team, you can update the foreign key and change the NULL to his new team

    simple, eh

    Hi,
    I prefer to use not null definition on foreign key column and use "no team" as 0 id. I like to use this method to have more simple select SQLs - there is no need of using left outer joins to get all players and belonging teams.
    Regards,
    Grofaty

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by grofaty
    I prefer to use not null definition on foreign key column and use "no team" as 0 id.
    to me, that's horrible

    your SQL needs more fudging than mine, because when you write queries that involve teams, you must always remember to filter out the "no team" team
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by grofaty
    Hi,
    I prefer to use not null definition on foreign key column and use "no team" as 0 id. I like to use this method to have more simple select SQLs - there is no need of using left outer joins to get all players and belonging teams.
    Regards,
    Grofaty
    That's just bad design.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    r937, stolze,
    it may be bad design, but a lot of problems are solved. I have seen a lot of analytical SQL tools that have problems if null is set on foreign keys. For example if table has some analytical data for example sales per month (dependent table) and having no customer (parent table), sums are not correct, because there are eliminated some data that doesn't have customer.

    There is also one more benefit - not null fields are using one additional byte per row. Having millions of rows that can be a huge save...

    Regards,
    Grofaty

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "it may be bad design, but a lot of problems are solved" -- grofaty

    this goes into my collection called Famous Last Words
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by grofaty
    There is also one more benefit - not null fields are using one additional byte per row. Having millions of rows that can be a huge save...
    With compression being available in pretty much every database system these days, I'd count this argument as moot.

    So it comes down to the question whether you want to have a sound design and are open for future development or try to work around problems in some tools. For example, tooling issues could be addressed with views that map NULLs to 0 (zero) and still allow you a proper design.
    Last edited by stolze; 06-23-08 at 12:19.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    The most important argument I have just forgot to mention. In our company we use DB2/Linux/zSeries and DB2/VSE/zSeries. The last one only supports simple SQLs no left outer join etc, so using null on foreign key makes things much much more difficult. Data from tables are constantly moving from one system to another - so a null on foreign key makes a lot more time to write SQLs.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by grofaty
    The last one only supports simple SQLs no left outer join etc, ...
    my condolences

    please don't let this horrible experience influence your idea of good design

    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
  •