deleting data

  • Thread starter Thread starter Roy Goldhammer
  • Start date Start date
R

Roy Goldhammer

Hello there

I've build query in access that bound for more then one table.

If i delete record from the query. it it not only delete the master query
but all the related tables.

This thing is catasfrofy. is there a way to keep the joins in the query and
on the detete action to delete only the master table and not the other
related tables?
 
No. That would violate the integrity of the database.

For example, Northwind has:
- an Orders table (the header record for the customer order),
- an OrderDetails table (the line items of the orders.)
The OrderID number is the common field.

If you deleted OrderID 99 from the Orders table (the master table), but left
all the line items for order 99 in OrderDetails (the related table), you
would now have orphan records that don't belong to any order. You would not
know whose order this was, when it was ordered, where it was to be shipped
etc. All you would have is meaningless, orphaned records, for an invalid
order number.

If you do have a scenario where you need to keep the child records when the
parent is deleted, you should at least set the foreign key field to Null so
they do not relate to an invalid number. Programmatically, you can set up
the relationship as cascade-to-null, but I can't promise the inner join
query will work as you expect.

For details on how to set up such a relationship, see:
Cascade-to-Null Relations
at:
http://allenbrowne.com/ser-64.html
 
Whell allen.

I don't have any relationships in my database. The only problem is in the
query

Here is example of what i'm talking about.

I have customers table and order table like northwind.

I also have another table of Special customers i keep for diffrent purpose

I have automatic procedure that lodes all the data from customers table to
SpecialCustomer table. and on the form i present it i can change data on
SpecialCustomer table.

To make the form be fast i join to SpecialCustomerTable the customers and
orders table.

The problem happen when the user deleted in the form one of the records in
the query boud the the form that should be deleted from the SpecialCustomers
table.

but the record from the Customers table and orders table has been deleted as
whell. And this is what i need to be blocked. Can you help me on it?
 
Okay the solution will be to build a relational design.

I'm not clear what Special Customers are, what they have in common, why they
are different to other customers, or why they are in a different table. It
seems to me that it would be better to have a single table of customers,
with a check box (yes/no field) to indicate if this is a special customer or
not. This solves all the problems:
- It avoids the need to copy data from one table to another.
- You no longer have duplicated data (2 places where you must update the
same information.)
- The CustomerID foreign key in your orders table relates to one field in
one table, and you can use referential integrity.

If there are lots of other fields to keep for special customers that you
don't keep for ordinary customers, you might need to create another table
for those, linked one-to-one to your Customer table. SpecialCustomers is the
*related* table here (i.e. you can have a customer who is not a special
customer, but you can't have a special customer who is not a customer.) I
doubt you really need this, but I can't be sure from here.

Once the right relational structure is in place, the problem of bad data
disappears, along with the need to delete duplicated data across tables
leaving some in place while the other is not.

Hope that's clear enough. As always, you have more in mind than is explained
in a newsgroup posting, but I hope the principle is clear.
 
Back
Top