Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Keys, foreign key?

    I need to know if this is required? I have products, with the help of their business account numbers, are naturally categorized numerically. I want to create a product category table and a product account table.

    Code:
    Example :
    tbProductCategories
    TypeCode   | Description
    1000           | Cups
    2000           | Plates
    
    tbProductAccounts
    Account | Description
    1001     |  Mug
    1002     | Glass
    2001     | Plate
    2002     | Saucer
    With the above tables (which are made up ), would you include
    a foreign key in tbProductAccounts indicating the type code?

    What would the stored proc look like without it?

    Code:
    Create Procedure usp_GetProductAccounts
    
    @iTypeCode int
    
    AS
    
    SELECT tbProductAccounts.Account, 
                tbProductAccounts.Description
    FROM tbProductAccounts
    WHERE tbProductAccounts.Account - iTypeCode > 0 AND < 999
    Would this work? Or should the foriegn key always be included?

    Mike B

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In my opinion, you should always, always, always include the foreign key. What happens when someday the boss demands a part number of "clyde" for a cup with a clydesdale on it because he promised it to a vendor?

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by Pat Phelan
    In my opinion, you should always, always, always include the foreign key. What happens when someday the boss demands a part number of "clyde" for a cup with a clydesdale on it because he promised it to a vendor?

    -PatP
    Then that would not be clyde, but possibly 1010?

    lol, I understand what you are saying. I think I knew that already, especially after I placed the table in the diagram an noticed there was no "true" relationship!

    Thanks
    Mike B

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    Another reason to keep the foriegn key I guess is also to ensure data entegrity without having to add constraints in the stored proc or elsewhere.

    Mike B

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Talking

    another reason to support foreign keys is the benefit of merge joins in multi-table queries.
    when you have to sorted columns in two tables and they are the columns in the join predicate
    (on c.col1 = p.col2)
    SQL Server will perform a merge join.
    this is one of the fastest join processes available.
    to encourage this you should always create non-clustered indexes on your foreign key columns.

    {BOOKS ONLINE}Understanding Merge Joins

    just another angle on this question seein' as all of the good points were taken.
    Last edited by Ruprect; 03-11-04 at 00:58.

Posting Permissions

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