UNIQUE constraints on many-to-many tables

[Uncle Buddy]

While rewriting the notes dialog, I found that it was possible to add the same pair of foreign keys to the notes_links table more than once, such as the same note_id and event_id pair. This has caused me to think about adding UNIQUE constraints to all my junction tables.

Question 1: Is it really a many-to-many relationship? Yes. Each note can be linked to many events, and each event can be linked to many notes. It's just that each element can only be linked to the same note once.

So this is OK because that's what many-many tables are for:

note_id	event_id
12	98
127	42

But this is undesirable since it serves no purpose and might create bugs:

note_id	event_id
12	98
12	98

Question 2: Will a UNIQUE constraint in the table schema solve the problem? Such as `UNIQUE (note_id, event_id)`.

I don't think it will. If the user tries to link a note to an element that the note is already linked to, a SQLite error will occur due to the UNIQUE constraint and the user has no use for that.

Adding UNIQUE constraints to all the possible pairings of note_id with other element IDs in the notes_links table would double the size of the table schema. This is not a problem if it's necessary but I don't see the advantage. A similar problem exists in media_links and roles_links.

Instead of complicating the UNIGEDS schema and then having to complicate the Treebard code to handle SQLite errors, I think the right approach is to add Python code to make it impossible for the user to insert the same link to the table twice. Also, my philosophy on messages, notifications and R-U-Sures is to keep things simple silently when possible. So if the user tries to link a note to an event when that note and event are already linked, I would just return on the function and not do anything, and not annoy the user with a message about his mistake.

It boils down to this: we are not here to fiddle with software. We're trying to do some genealogy here.

[Uncle Buddy]

The SELECT COUNT query and second guard clause ending in `return` solved this problem the easy way:

   def link_selected_note(self):
       if self.selected is None:
           return
       note_id = int(self.selected["text"])
       conn = sqlite3.connect(
           f"{tree_path}/{self.directory}/{self.directory}.tbd")
       conn.execute('PRAGMA foreign_keys = 1')
       cur = conn.cursor()
       cur.execute(
           f''' SELECT COUNT (notes_links_id)
               FROM notes_links
               WHERE note_id = ? AND {self.linked_element}_id = ?
           ''',
           (note_id, self.element_id))
       if cur.fetchone()[0] != 0:
           print("duplicate insert cancelled")
           return
       cur.execute(self.insert_notes_links_new_note, (self.element_id, note_id))
       conn.commit()
       cur.close()
       conn.close()