CODEWITHSUNDEEP
c#mysql
harikrishnan.n0077
harikrishnan.n0077

Reputation: 2047

Insert values in both related tables MySQL

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:

  1. 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):

  1. 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?

Upvotes: 0

Views: 47

Answers (2)

Delphi.Boy
Delphi.Boy

Reputation: 1216

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().

Upvotes: 1

MeshBoy
MeshBoy

Reputation: 692

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.

Upvotes: 1

Related Questions