Referential integrity and junction tables

  • Thread starter Thread starter Miguel Alexandre via AccessMonster.com
  • Start date Start date
M

Miguel Alexandre via AccessMonster.com

I am working on a database and I clearly need to create a many-to-many
relationship. My doubts are related to how referential integrity works in
regards to the junction table and can be generally stated with the following
questions:

Assuming that referential integrity is "cascade" enforced:

1. If the junction table has got only the primary key fields from the parent
tables, how does a delete (update) on a parent table affect the records on
the other parent table?

2. If the junction table has got more fields besides the primary keys from
the parent tables, how does a delete (update) affect these fields?

Thank you very much for your support.

Miguel Alexandre
 
Cascade only works downstream as it were.

If you delete one record in a "master" table it will delete the
corresponding record in the "junction" table, it will not affect the other
record from the other "master" table. If you were using SQL Server you could
run a stored procedure that could delete the other "master" record.

It matters not how many fields are in the "junction" it will (the
corresponding record) be deleted if Cascade Delete is asserted.
 
Thank you for your reply. That's exactly what I wanted to know!
Cascade only works downstream as it were.

If you delete one record in a "master" table it will delete the
corresponding record in the "junction" table, it will not affect the other
record from the other "master" table. If you were using SQL Server you could
run a stored procedure that could delete the other "master" record.

It matters not how many fields are in the "junction" it will (the
corresponding record) be deleted if Cascade Delete is asserted.
I am working on a database and I clearly need to create a many-to-many
relationship. My doubts are related to how referential integrity works in
Click to expand...
[quoted text clipped - 15 lines]
Miguel Alexandre
Click to expand...
Click to expand...
 
Thank you for your reply. That's exactly what I wanted to know!

Miguel Alexandre
Cascade only works downstream as it were.

If you delete one record in a "master" table it will delete the
corresponding record in the "junction" table, it will not affect the other
record from the other "master" table. If you were using SQL Server you could
run a stored procedure that could delete the other "master" record.

It matters not how many fields are in the "junction" it will (the
corresponding record) be deleted if Cascade Delete is asserted.
I am working on a database and I clearly need to create a many-to-many
relationship. My doubts are related to how referential integrity works in
Click to expand...
[quoted text clipped - 15 lines]
Miguel Alexandre
Click to expand...
Click to expand...
 
Back
Top