Question about Relationships

[Djblois]

I was wondering if I there is a benefit to using them? Currently I have one table that has Scheduled_Appts_Table which I attach to an intermediary table Outgoing_Orders_Table and attach that to actually orders table. However, I do this in a query is there any benefit to me setting up a relationship between these tables? any drawbacks? I have a lot more that are set up as relationships in queries but I don't have any set up as table relationships. Should I? And if I do, do I need to keep the relationships in the queries or is there another way to perform them then?

 

[WIS]

I was wondering if I there is a benefit to using them? Currently I have one table that has Scheduled_Appts_Table which I attach to an intermediary table Outgoing_Orders_Table and attach that to actually orders table. However, I do this in a query is there any benefit to me setting up a relationship between these tables? any drawbacks? I have a lot more that are set up as relationships in queries but I don't have any set up as table relationships. Should I? And if I do, do I need to keep the relationships in the queries or is there another way to perform them then?

Relationships between tbls (with RI set) prevent things such as "orphaned" records - you delete a record in the main (PK) tbl and leave a no of associated records in the secondary (FK) tbl.

With a couple of tbls this may not be a big issue, but with a 100 or more tbls things can get out of hand without relationships being set. I almost use RI exclusively - I keep out of "Cascading Deletes" especially as some users can be very slack.

As far as drawbacks are concerned I really haven't come across any that are problematic, but I'm sure there'd be some.

Perhaps some others on this forum can elaborate more.

 

[Djblois]

1) What will happen if I delete a Carrier from the Carrier table if their is a relationship with a few appointments? will it delete the appointments or will it stop me from deleting them?

2) now if I create a relationship between the tables do I also need to create relationships in the queries also?

 

[WIS]

1) What will happen if I delete a Carrier from the Carrier table if their is a relationship with a few appointments? will it delete the appointments or will it stop me from deleting them?

1. With relationship set and no RI etc you can delete from Carrier but it will leave the few appts as "orphaned" records.

2. With RI set you have to delete the appts before it will let you delete the Carrier.

3. With "Cascade Deletes" set, if you delete the Carrier it also deletes any assoc records in the appts tbl.

2) now if I create a relationship between the tables do I also need to create relationships in the queries also?

No - it will automatically show relationships in the qrys.