Insert values in both related tables MySQL

Question

I have two tables:

create table `db_csms`.`tbl_item_requester`
(
    `id` int not null,
    `item_id` int not null,
    `requester_id` int not null,
    foreign key(`item_id`) references `db_csms`.`tbl_items`(`item_id`),
    foreign key(`requester_id`) references `db_csms`.`tbl_user_details`(`user_id`),
    primary key (`id`)
);
create table `db_csms`.`tbl_item_requests`
(
    `id` int not null,
    `item_requester_id` int not null,
    `quantity` int(5) not null default 0,
    foreign key(`item_requester_id`) references `db_csms`.`tbl_item_requester`(`id`),
    primary key (`id`)
);

tbl_items and tbl_user_details are already populated with values. My problem is when a new row is added into table 1 because the table 2 uses the id of that new row inserted in table 1.

My issues are:

How to get the newly inserted row id of table 1 which is needed for inserting in table 2.

Strategy to solve this issue(my thinking):

Remove auto_increment and then generate a random value (using C# code) and use that value in both tables.

Are there any workaround to this problem? Do i have to change my strategy? Is the Database design incorrect?

Use last_insert_id(): dev.mysql.com/doc/refman/5.7/en/…. — Gordon Linoff
– Gordon Linoff, Commented Apr 6, 2015 at 12:07
@GordonLinoff does that return the correct id for multiple inserts from different clients — harikrishnan.n0077
– harikrishnan.n0077, Commented Apr 6, 2015 at 12:11
You have C# as a tag. How are you inserting records? SqlCommand? Linq-to-Sql? EF? Please show your c# code. ORMs provide the auto-generated id automatically. — Delphi.Boy
– Delphi.Boy, Commented Apr 6, 2015 at 12:17
Your question is specifically in the singular: "How to get the newly inserted row id". However, if you have multiple ids inserted in a single statement, they should be consecutive. — Gordon Linoff
– Gordon Linoff, Commented Apr 6, 2015 at 12:17

MeshBoy · Accepted Answer · 2015-04-06 12:13:19Z

1

Since you're using MySQL as your database, there is the specific function LAST_INSERT_ID() which only works on the current connection that did the insert.

answered Apr 6, 2015 at 12:13

MeshBoy

6926 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Delphi.Boy · Accepted Answer · 2015-04-06 12:33:55Z

1

In case of SQL Server you could write:

Insert .... Values(....); Select Scope_Identity()

and use SqlCommand.ExecuteScalar() that returns the first value of the first row, which would be the ID of newly inserted row. In MySql, you should be able to write last_insert_id() instead of Scope_identity().

answered Apr 6, 2015 at 12:33

Delphi.Boy

1,2264 gold badges21 silver badges42 bronze badges

Collectives™ on Stack Overflow

Insert values in both related tables MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related