Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2012
    Posts
    32

    Unanswered: Database design - cannot delete from multiple tables

    Hi all!

    I would like to ask you to help me with database design.

    I have tables like this:
    tbl_Countries (PK country_id)
    tbl_Cities (country_id, PK city_id)
    tbl_People (city_id, PK person_id)
    tbl_Cars (person_id, PK car_id)

    (Of course these are not the actual data, I just thought it might be easier to illustrate)

    Data conditions are:
    every country can contain multiple cities OR NULL
    every city can contain multiple people OR NULL
    every person can own multiple cars OR NULL

    And the problem is:
    What if I want to delete an entire country with all the related cities, people and cars? If I build a query like

    countries INNER JOIN cities INNER JOIN people INNER JOIN cars

    I can easily delete the records with any country I wish, only the inner joins will leave those cases where person doesn't have a car, city doesn't have a person or a country doesn't have a city.

    And if I use left joins the delete queries would fail.

    My question is: Did I do something wrong? Is my whole database design incorrect? Shall I put all these data into one big table... and if so, how to design it?

    I could do this using VBA, but I'd rather not to.

    Many thanks for any advice.

    Marek

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you create relationships among the tables and, if yes, did you define integrity constraints?
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Or use delete cascade on your rekationships. But you need to carefully consider the impact of using delete cascade. Personally I find it rekativel rare when it is usefull
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2012
    Posts
    32
    Hi Sinndho, healdem,

    I admit I'm not used to using relationships, so I might have set them up incorrectly, on the other hand, I don't see where I might have messed it up. All relationships are cascading and of type LEFT JOIN (please see the picture attached).

    I have also attached the database I have created from scratch just to check this problem.

    The goal would be to run the delete query without error.

    Many thanks for your time.
    Attached Thumbnails Attached Thumbnails DeleteProblem.jpg  
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So have you selected the on delete cascade option in your relationship definition.
    Last edited by healdem; 05-28-14 at 07:06.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2012
    Posts
    32
    Quote Originally Posted by healdem View Post
    So have you selected the on delete cascade option in your relationship definition.
    Yes, I did.

  7. #7
    Join Date
    Nov 2012
    Posts
    32
    Oh, now I finally get it!

    I had the relationships set correctly, I was just trying to delete from query instead of from the table itself... because I did not know how Access handles cascading.

    Now I understand, thank you for your help!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Myshaak View Post
    Oh, now I finally get it!

    I had the relationships set correctly, I was just trying to delete from query instead of from the table itself... because I did not know how Access handles cascading.
    ? a query is a means of manipulating data, unless you specify a WHERE clause the query will attempt to process all rows.
    so a delete query with no where will delete all rows

    if you have defined an on delete cascade for all the child realtionships, deleting one or more rows will also delete any rows in child tables that have the same foreign key value.

    delete cascade is very very dangerous, unless you knwo what you are doing AND have a good business reason to use it. in days of old when disk space was tight there were often good reasosns to delete data. these days organisations are more concerned with analystics.

    deleting a customer may seem attractive, but if the custoemr has linked orders, with linked sales, then delting a customer ALSO deletes the orders and order details for that customer. meaning if you try to run comparatives then you understate the sales. you make comparatives accross the month/year etc meaningless.

    in current times usually Id set a column which identifies if the customer is still a live account. their purchasing data remains but they no longer appear for sales order procerssing
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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