Bridging Table Properties

Question

I have a question on bridging tables: if I have a bridging table linking two tables (standard) with the only values in the bridging table being the Primary Keys for both tables it is linking, how would I create new records in the bridging table if I wanted to, would this be through simply adding them in to the table directly as long as they existed in both the Parent tables?
Also, under what circumstances can something be deleted from a bridging table (or not be)?
If anyone has any references on where to find more info to read up on this as well, I would appreciate it.
Thanks

Rick James · Answer

True.  You simply do
INSERT INTO bridge (id_a, id_b)
    VALUES (?, ?);

If there is some chance you have already inserted it, you could use INSERT IGNORE instead of INSERT.  (You should have PRIMARY KEY(id_a, id_b), which is a uniqueness constraint.)
Tips on the schema for the table:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
Deletion from the table should happen (manually) when either of the linked rows is deleted.  But if this delete does not happen, there is little or no harm.  (See also LEFT JOIN to get NULLs when a row is missing.)
Do not use such a table for 1:many or 1:1 mappings, only for many:many.

nbk · Answer

You add a row into the bridging table , when you need to connect two rows from the referenced tables.
you can always delete one row in the bridging table, when that connection isn't any longer present.
What you are searching for is Associative entity

Bridging Table Properties

2 Answers

Add your own answers!

Ask a Question