Question JOIN types?

[dbmanalser84]

Where can you best see the application of LEFT and RIGHT JOIN while Refferential Integrity is on in the relationship between the two tables?

I can clearly see this while Referential Integrity is not applied, because in that case it is allowed for data from one table not to have their counterparts in other table, so you can clearly see when you apply any JOIN type (LEFT or RIGHT).
When Referential Integrity is on all data from one table must have their counbterparts in other table, so how can LEFT or RIGHT JOIN (when applied) can be seen there, when that situation resembles of INNER JOIN?

While the option CASCADE DELETE (and Referential Integrity) is turned on, will the way of deleting records be changed in any of the two tables when you change the way of JOIN between them?

 

[redneckgeek]

Referential Integrity just means that a record can't be in the child table without first being in the parent table. It doesn't mean that every record in the parent table must also exist in the child table. Think of it as a
one-to-none relationship, as opposed to one-to-one or one-to-many.

 

[neileg]

Referential integrity doesn't require a match for every record on the 1 side of a 1 to many relationship. So a left join is perfectly valid. A right join will return the same records as an inner join.

Edit: Drat, beaten to it!

 

[dbmanalser84]

Ok. And can you explain a CASCADE DELETE?

If you delete a PARENT in parent table will all CHILDS be deleted and vice versa? And how (and does) the JOIN (any) effects this?

 

[redneckgeek]

You can't have cascade updates or deletes if you don't have a join. The join defines how one table is related to another. Cascades are one-way.
A record deleted in a parent table will delete all related records in child tables, but a record deleted in a child table will have no effect on the parent.

 

[gemma-the-husky]

if you have a customers table linked to an orders table

then you can find

a) the customes WITH orders
b) the customers WITHOUT orders
c) all customers, with order details where applicable,

by selecting the appropriate join. without checking the SQL, i am never sure which one is which, but in the query desing window, the difference is the direction of the arrow.

if you have set up ref. integerity, so that you HAVE to have a customer if you have an order, then deleting a customer would not be possible, if you have orders for that customer. In those cases a cascading delete would delete the orders as well as the customer

I find this really dangerous, and would never use it - you can actually use it the other way - if you DONT allow cascading deletes then access will stop you deleting the customer, BECAUSE there are realted records in the orders table.

Cascading updates is a similar thing - if you cahnge the key for the customer, it would cascade into the orders table - not as drastic, but best avoided by better key design - ie design a system where you would never need ot change a key field

 

[Pat Hartman]

Cascade delete is not intended to be used on all parent-child relationships. As with any tool, you must apply common sense. It makes perfect sense to specify cascade delete on the relationship between orders and order details. If you delete an order you will always want to delete the details. You may have business rules that come into play and you need to provide for them via code. For example, a shipped order may not be deleted but an unshipped one may. Conversely, you would never apply cascade delete to a lookup relationship such as Order to State. Just because there is a one-to-many relationship between the state table and the billing/shipping state field of an order doesn't mean that you would apply cascade delete to that relationship.