SQLite，Golang和联结表

I would like to create a small database of books using Go and sqlite. I took the main from this advise SQLite foreign key examples and redeveloped it a bit.

package main                                                                                                                                                

import (
    "database/sql"
...
    _ "github.com/mattn/go-sqlite3"
)

...
db, err := sql.Open("sqlite3", "./foo.db")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

sqlStmt := `
    create table books (
        id integer primary key autoincrement, 
        title text
    );

    create table booksauthors ( 
        bookid integer references books(id), 
        uthorid integer references authors(id) ON DELETE CASCADE ON UPDATE CASCADE
    );

    create table authors (
        id integer primary key autoincrement, 
        fname text, 
        mname text,
        lname text,
        unique (fname, mname, lname) on conflict ignore
    );
`

So, I'd like to keep list of unique authors and maintain many-to-many connection with books tables (one book may have more then one author, and author might write more then one book).

Then I simply add books in cycle, get LastIndexID and put it to junction table (code is reduces for illustration, b is book struct):

tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}

res, err := db.Exec("Insert into books(title) values(?)", b.Title)
if err != nil {
    log.Fatal(err)
}
b_id, _ := res.LastInsertId()
for _, a := range b.Authors {
    res, err = db.Exec("Insert into authors(fname, mname, lname) values(?, ?, ?)", a.Fname, a.Mname, a.Lname)
    if err != nil {
        log.Fatal(err)
    }
    a_id, _ := res.LastInsertId()
    fmt.Println(a_id, b_id, a)

    res, err = db.Exec("Insert into booksauthors(bookid, authorid) values(?, ?)", b_id, a_id)
    if err != nil {
        log.Fatal(err)
    }
}
tx.Commit()

By here comes trouble - a_id is increased if I add more then one book of the same author, but junctions table contains older value of it. For example:

Books:                       
id | Title                   
---|--------------------
1  | Atlas Shrugged pt.1 
2  | Atlas Shrugged pt.2
3  | Atlas Shrugged pt.3

Authors:
id | Fname | Mname | Lname 
---|-------|-------|------
702| Ayn   |       | Rand

Junction table:
BookId | AuthorID
-------|---------
 1     |  700
 2     |  701
 3     |  702

What I want - Junction table:
BookId | AuthorID
-------|---------
 1     |  702
 2     |  702
 3     |  702

How can I fix it so correct AuthorId would be reflected at the table? I don't want use GORM or any of ORM tools and trying to solve it using pure (well, more or less) SQL.

One of the solutions I see now I can first SELECT, then INSERT if nothing is found and then SELECT once again, however I'm not sure how idiomatic this idea is. Please note that I have considerable numbers of records to add.

My advice would be to SELECT you list of authors and INSERT the ones that are missing. The solution is naive, but it works and is simple. I'm not up-to-date with SQLite relationnal features, but using them isn't always the simplest wway to deal with foreign keys….

Now, if you have a big bulk of data to process, do the same operation but start by merging your books authors so you can have only one SELECT then one INSERT to do.

As a quick'n'dirty hack I did a select after adding an author:

err = db.QueryRow("select id from authors where fname = ? and mname = ? and lname = ?", a.Fname, a.Mname, a.Lname).Scan(&a_id)
if err != nil {
    log.Fatal(err)
}
res, err = db.Exec("Insert into booksauthors(bookid, authorid) values(?, ?)", b_id, a_id)
if err != nil {
    enter code herelog.Fatal(err)
}

But I'm still looking for better answer...