It sounds likely you have cascade deletes setup. It also sound like you are
enforced referential integrity here.

Really, all you have is a lookup. Lets say a table of favourite colors.

If you delete a few records of bob's favourite colors...you don't care, or
need any relation stuff to the "colors" table. It is not really a formal
relationship..but just a list you use for looking up colors to save typing.

So, go to your relationship window..and double click on the join link
between those two tables...you want to un-check the cascade deletes. You can
leave in the referential integrity (enforce)...but you don't really have to.

Fact is, in our color example...you are free to add new colors to the
table...and again many customers may not yet have chosen their favourite
color. So, you code of want to distinguish between real relations like a
customer who has many orders. If you delete that customer, then you most
certainly want all orders that belong to that customer removed also. And,
you also don't want to be able to add an order UNLESS the customer exists.

However, with our color example...we have a lot of freedom..and we can add
new colors to our color list, and we can delete customers..but we never
delete colors from that simple list of colors. So, it is rather more of a
lookup list..then a relation. In fact, you can get away not even drawing any
join lines in the relationship window in this case. However, it is handy to
leave them there...as it helps you see that some tables do have a lookup.

If you take look at the following screen shot..you can see that when tables
do NOT have the sideways 8 (omega), then we are talking only about a
lookup. And, if you look even closer...you will note that some lines have an
arrow head..but some don't These are enforced relations..but what we call
left joins.

for example...we might set the database up so that all customers MUST have a
order. But, likely that is too restrictive of a relation (so, we use a left
join...or what the prompt says:

Include all records from customers and only those records from table orders.

If you set the relationship to where joined fields are both equal..then in
effect you are saying that customers MUST have a order for the database to
function. As a result, looking at the following relations picture...90% of
all my relations are left joins (yours should be also).

http://www.attcanada.net/%7ekallal.m...Appendex2.html


So, for example, tblBgroup (booking Group), is linked to table
payments...note the right arrow. That means, as a developer I accept the
fact that no payments may not yet be made. However, look at how tblBgroup is
joined to tblBooking. So, I do NOT allow a "group of people" to be booked
until a booking record is made. Thus, all my code will assume when you
create a booking..you MUST ADD people (the booking group).

So, setting up your relationships diagram can result is HUGE amount of
documentation for the project. In fact, I have as consultant walked into
companies and had developers fired for lack for setting up
relationships..and using ER tools. It is the first thing I look for when
evalaution any projects..or skills of the develoeprs involved.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn