Referential integrity and junction tables

[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

 

[Craig Alexander Morrison]

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.

 

[Miguel Alexandre via AccessMonster.com]

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

[quoted text clipped - 15 lines]

Miguel Alexandre

 

[Miguel Alexandre via AccessMonster.com]

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

[quoted text clipped - 15 lines]

Miguel Alexandre