MySQL ON DUPLICATE insert into another table

Question

I have the following problem: Every author has a name and n-aliases for this name. Every alias comes from a source. Every alias has m sources. For example

AUTHOR| ALIAS | SOURCE
-------------------------
Will| Willy |George
Will| Bill  | Jenny
William| Will|  Francis
William| Bill| Maya

I have one table for the author and his name, one for all of his aliases:

CREATE TABLE alias (
    authors_id INT NOT NULL,
    alias VARCHAR(150) NOT NULL,
    id INT NOT NULL AUTO_INCREMENT
    PRIMARY KEY (author_id,alias);

id serves as foreign key. Here's the second table for the sources

CREATE TABLE alias_source (
    id INT NOT NULL AUTO_INCREMENT
    source VARCHAR(150) NOT NULL,
    alias_id INT NOT NULL,
    PRIMARY KEY (id)
    FOREIGN KEY (alias_id) REFERENCES alias(id);

Now I need an MySQL insert statement for when I insert author,alias,source into alias that the source is inserted into alias_source. And on duplicate alias no only a new source is added.

Answer 1

An INSERT statement in SQL can only insert into one table, and can only list columns of that one table. You can't include your source column in the INSERT statement, because it's not a column of the alias table.

Triggers can allow you to insert into a secondary table, but the trigger needs to know the value to insert. In this case the trigger doesn't have any way of getting the value for source.

This is a task that is much easier to do with two INSERT statements.

INSERT IGNORE INTO alias SET author_id = ?, alias = ?;
INSERT INTO alias_source ...

But you have a problem because your alias table has an auto-increment column id, but this column is not part of a key. In InnoDB, you must make the auto-increment column the first column of a key.

Your alias_source table has a foreign key referencing alias(id) but that's not allowed. A foreign key must reference a key of the parent table. It should reference the unique or primary key, and should reference all columns of the key (otherwise you get a row in a child table that might reference multiple rows in the parent table, and that makes no sense).

If you want to use an auto-increment column for your alias table, make it the primary key, and put a secondary UNIQUE constraint on the other columns.

CREATE TABLE alias (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    authors_id INT NOT NULL,
    alias VARCHAR(150) NOT NULL,
    UNIQUE KEY (author_id,alias));

Then query the id after you insert. Whether you inserted a new row, or the row already existed, you'll get the id for the row with the chosen author and alias.

INSERT IGNORE INTO alias SET author_id = ?, alias = ?;
SELECT id FROM alias WHERE author_id = ? AND alias = ? INTO @id;
INSERT INTO alias_source SET alias_id = @id, source = ?; 

---

Re your followup question in comment:

Good idea, but it doesn't work the way you may think it does. You can do a dummy set of id=id and set a session variable as a side effect.

mysql> insert into alias set author_id=1, alias='alias' 
    on duplicate key update id = @id:=id;

mysql> select @id;
+------+
| @id  |
+------+
| NULL |
+------+

But why didn't it set the session variable to the id? Because this insert was not a duplicate, it was a new row. So the id value had not yet been generated by auto-increment at the time the IODKU executed.

If you subsequently do the same IODKU for a row that is a duplicate, the id value has previously been added to the row, so you get the value in the side-effect:

mysql> insert into alias set author_id=1, alias='alias' 
    on duplicate key update id = @id:=id;

mysql> select @id;
+------+
| @id  |
+------+
|    1 |
+------+

So you'd have to write application code anyway to check if @id is NULL or not, and do a SELECT id query if @id is NULL.

That seems more complicated than just doing the SELECT id as a standard action following the INSERT IGNORE like I showed above.