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

1
  • 1
    This might be helpful. Another link which might help is here - see this for a reference about DELETEing! – Vérace Jul 20 '20 at 3:25
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.

8
  • 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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.