Inserting into multiple tables at once

Question

Structure:

Table A)

CREATE TABLE Item (
  Id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Unique id of an item',
  `By` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (Id),
  CONSTRAINT FK_Item_User_Name FOREIGN KEY (`By`)
    REFERENCES User(Name) ON DELETE NO ACTION ON UPDATE CASCADE
)

Table B)

CREATE TABLE ItemName (
  Item_Id INT(11) UNSIGNED NOT NULL COMMENT 'Item id this name is referencing',
  Language VARCHAR(5) NOT NULL COMMENT 'language code by ISO 639-2/T',
  Translation VARCHAR(50) NOT NULL COMMENT 'Item name for given language',
  PRIMARY KEY (Item_Id, Language),
  CONSTRAINT FK_ItemName_Item_Id FOREIGN KEY (Item_Id)
    REFERENCES Item(Id) ON DELETE CASCADE ON UPDATE CASCADE
)

Table C)

CREATE TABLE User (
  Name VARCHAR(50) NOT NULL,
  Password VARCHAR(50) NOT NULL,
  Salt VARCHAR(255) NOT NULL,
  Blocked TINYINT(1) DEFAULT 0,
  PRIMARY KEY (Name),
  UNIQUE INDEX UK_User_Name (Name)
)

Question:

Now I want to insert a new item. Let's say the user provides us with:

Translation
Language code
Username

What i got so far:

I was thinking of puting it in a transaction and inserting into each table after eachother. But then i'm stuck on how do i know what Item.Id to use in the ItemName.Item_Id field since the Item table will AI a new Id for the insert.

I could get the last Item.Id, but then it might be pointing to another item if multiple users were to be inserting a new item simulaneuosly.

ps. Engine=InnoDB

I think this will help you http://stackoverflow.com/questions/21185666/mysql-last-insert-id-and-concurrency — Mihai, Oct 14 '15 at 17:30
thx, [link](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) howevery helped me more. — Koen Demonie, Oct 15 '15 at 17:57
What API are you using? PHP's mysqli, for example, has last_insert_id(). — Rick James, Oct 20 '15 at 00:17
Php mysqli. Sorry i thought this would be achieved on query level. — Koen Demonie, Oct 20 '15 at 00:19

score 0 · Answer 1 · answered Oct 20 '15 at 00:19

0

BEGIN;
INSERT into one table;
get the last_insert_id() -- there are many ways to do this, depending on the API
INSERT into next table...
... ;
COMMIT;

Do the inserts in the order that avoids violating your FOREIGN KEYs.

answered Oct 20 '15 at 00:19

Rick James

135,179
13
127
222

Inserting into multiple tables at once

Structure:

Question:

What i got so far:

1 Answers1