SQL insert foreign keys if not already present while adding a new record

Question

Suppose I have a table 'Note':

PK id: int
note: varchar
FK tags: Tags[]

and another table 'Tags":

PK id: int
tag: varchar
FK note: Note[]

Now, if I want to add a new record, say:

{

 "note": "Hello World!, this is an example note",
 "tags": ['first','example']
}

Is there a way to do it in a single request? Or, Do I have to save the tags and make another request with their ids?

MySQL doesn't have an `int[]` data type. Perhaps you're using a different brand of database? — Bill Karwin, Aug 07 '22 at 16:06
@BillKarwin sorry, I meant that they have a many-to-many relationship. — Prabin, Aug 07 '22 at 16:31

score 0 · Answer 1 · answered Aug 07 '22 at 16:40

If you have a many-to-many relationship, then you should have three tables:

'Note':

PK id: int
note: varchar

'Tags':

PK id: int
tag: varchar

'NoteTags':

note_id int
tag_id int
PK: (note_id, tag_id)

Do three inserts: one insert to Note, one insert to Tags, then one insert to NoteTags for the pair of id's used to associate a given Note to a given Tag.

This is the way to represent a many-to-many relationship. It's not a good idea to store comma-separated lists in a string.

SQL insert foreign keys if not already present while adding a new record

1 Answers1