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

    Join Date
    Dec 2009
    Posts
    334
    Rep Power
    246

    Keys / Relationships / Cascades


    hi,

    I would like a little help understanding how foreign key relationships and cascading operations work.

    If I have a master table 'Contacts' and a linked table 'HomeAdd'.

    If I create a foreign key in 'HomeAdd' to the 'Contacts' table and allow cascade deletes.

    Which table deletes from which?

    If I delete a record from 'Contacts', then I want the 'HomeAdd' record to also be deleted, but if I delete a record from 'HomeAdd' I DO NOT want the 'Contacts' record deleted.

    Can someone clarify which table the relationship should be created in to ensure the cascade is only one way and the correct way!

    Aside -> the 'HomeAdd' table is not in correct normal form as it has it's own primary auto increment key so technically the Contacts -> HomeAdd is 1 to many, but should be 1 to 1.

    Should I alter this / do I need to alter this before the correct relationship can be set up?

    Many thanks,
    1DMF
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by 1DMF
    If I delete a record from 'Contacts', then I want the 'HomeAdd' record to also be deleted, but if I delete a record from 'HomeAdd' I DO NOT want the 'Contacts' record deleted.
    this is exactly how it works

    deleting a "parent" row will also delete all related "child" rows (where the "child" has the FK to the "parent")


    Originally Posted by 1DMF
    Aside -> the 'HomeAdd' table is not in correct normal form as it has it's own primary auto increment key so technically the Contacts -> HomeAdd is 1 to many, but should be 1 to 1.
    if you really mean 1 to 1, combine the tables
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    334
    Rep Power
    246
    So which ever table has the FK, that is considered the child?

    And all deletes only cascade parent -> child and not the other way round?

    -------------------

    Combine the tables why would I want to do that, 2nd normal form still allows 1-1 relationships doesn't it?

    Well that's what the OU course I'm studying tells me.

    You don't have to a have a 1 to many relationship to create a parent/child table link?

    --------------------------
    Either way it is refusing to let me create the FK in the child table with the following error...

    Could not create constraint. See previous errors. Cascading foreign key 'Fk_Contacts' cannot be created where the referencing column 'Contacts.ContactID' is an identity column
    I don't understand of course the parent field i'm creating the link to is the PK in that table?

    What am I doing wrong?
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by 1DMF
    And all deletes only cascade parent -> child and not the other way round?
    corrrrrect


    Originally Posted by 1DMF
    Combine the tables why would I want to do that, 2nd normal form still allows 1-1 relationships doesn't it?
    2NF is about composite keys

    of course 1-1 relationships are possible, but there's no need for them when you can simply combine the tables


    Originally Posted by 1DMF
    You don't have to a have a 1 to many relationship to create a parent/child table link?
    that's correct, you can have a 1-1 relationship, but the child's FK must then also be the child's PK



    Originally Posted by 1DMF
    Either way it is refusing to let me create the FK in the child table with the following error...
    can't help you with this because i can't see your table layouts from here
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    334
    Rep Power
    246
    but the child's FK must then also be the child's PK
    Aha, as I thought hence my remark about the 1-1 but it having a separate PK (auto increment).

    Dang, changing the DB structure will break the front end application....

    But if a job is worth doing.... looks like I am doing to have to redesign the form on the user app front end.

    Many thanks r937!
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!

IMN logo majestic logo threadwatch logo seochat tools logo