1

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

zv426
  • 29
  • 5

2 Answers2

2

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

nbk
  • 8,191
  • 5
  • 13
  • 27
1

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.

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • I'm not sure I understand what you're saying in your first line. Would you be able to clarify? – zv426 Jul 20 '20 at 10:19
  • @portis123 - Sorry, I read something into your Question that is not there. I'll revise my Answer presently. – Rick James Jul 20 '20 at 19:54
  • Thanks, and when you say it should happen manually, what do you mean? Is it something you have to implement or should it happen automatically when one of the two records in the relationship is deleted? – zv426 Jul 21 '20 at 17:19
  • @portis123 - I don't think it is practical to have, say, a Trigger automatically adding it. I would write client code (eg PHP/Java/etc) to insert whatever is needed into the 3 tables. Or I might "manually" insert using some tool (commandline MySQL / phpmyadmin/etc) – Rick James Jul 21 '20 at 18:11
  • Thanks, I meant with regards to deleting – zv426 Jul 21 '20 at 20:22
  • It won't matter much if you simply leave old bridging rows. – Rick James Jul 21 '20 at 21:15
  • As it's in a sort of one-to-many relationship with each of the two tables it is bridging, does that not mean that records cannot be deleted from the "parent" tables it is bridging until the relevant items get deleted from the child bridging table, or does it work differently with a bridging table to a usual one-to-many relationship? Apologies if I'm asking elementary questions but I am still in the very early stages of learning the basics of databases at the moment – zv426 Jul 23 '20 at 15:23
  • @portis123 - With "foreign key constraints", you "cannot". Without such, you "should not". The important thing is whether you can fetch the data from your table and, secondarily, whether the fetch is 'fast'. – Rick James Jul 24 '20 at 04:10