For a many-to-many association between two tables tA and tB, an association table has been created containing the primary keys of the two tables, tC. If one row is deleted from tB, can I delete the associated row in tC using one SQL statement? Also, if I change an association, what is the best way to update the association table to reflect this change?
If you delete a row from tB, the related rows in tC will be automatically deleted if you originally declared the ON DELETE CASCADE option for the foreign key in tC. If you didn't, you must handle the deletes in tC yourself before deleting the row in tB, unless you didn't even declare foreign keys, in which case you can go ahead and delete the row in tB first, and then the rows in tC, if you remember to. Even if you do remember to, the database will be in an invalid state until you do delete them.
If the above explanation sounds too complicated, let's look at some sample data to get a better feel for it:
Team Member Person 111 Alpha 111 JJ JJ John 222 Beta 111 LL KK Karl 333 Gamma 222 KK LL Linda 444 Delta 222 MM MM Mary 333 JJ 333 LL 333 MM
The Member table is an association table that implements the many-to-many relationship between teams and people. Using your names, the Team table is tA, the Person table is tB, and the Member table is tC. Alpha team consists of John and Linda, and so on.
Now let's suppose we need to delete Mary from the Person table. Obviously, we also need to delete the two rows 222MM and 333MM. You can get the database to do this for you automatically if you declared the tables as follows:
create table Team ( TeamID smallint not null primary key , TeamName char(5) )
create table Person ( PersonID char(2) not null primary key , PersonName char(5) )
create table Member ( TeamID smallint not null , PersonID char(5) not null , primary key (TeamID, PersonID) , foreign key ( PersonID ) references Person on delete cascade , foreign key ( TeamID ) references Team on delete restrict )
The ON actions in the foreign key specifications refer to the referenced primary key. For the ON DELETE CASCADE example, when a primary key in the Person table is deleted (i.e. when a row in Person is deleted), then the delete action is "cascaded" to the foreign key, and all matching rows in Member are also deleted. Thus when you delete Mary, the rows identifying her as a member of Beta and Gamma teams are also deleted automatically.
For the ON DELETE RESTRICT example, when a primary key in the Team table is deleted, the database checks to see if there are any matching rows in Member, and the delete will fail if there are. Thus you cannot delete Gamma, but you can delete Delta, which has no members.
This response is continued.