Solved

Inserting data into a MySQL(InnoDB) table with an auto-increment, foreign key

Posted on 2008-06-10
3
931 Views
Last Modified: 2008-06-10
Hi Experts,

I'm working on a new database which contains a few foreign key relationships. one particular relationship has a parent table with an auto-increment primary key which is used as the foreign key for another table.

here is a quick overview of the two tables:

Batch                                             Record_Counts
--------------                                    -------------------
(PK) Batch_ID                                (PK, FK)Batch_ID_FK
Creation_Date                                E_Record_Count
Batch_Number                               O_Record_Count
.....                                                  ........

now, given that the PK/FK Batch_ID is a auto-increment entry, what would be the best way to insert
the record_counts entry that corresponds to the batch table entry?
0
Comment
Question by:myabko
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Expert Comment

by:fridom
ID: 21757055
I'm not getting the trouble spot.

insert into Record_Counts (Batch_ID_FK)
       select Batch_ID where
        Creation_Date = "2008-03-21" and
       Batch_number = 10;


or the like

Regards
Friedrich
0
 

Author Comment

by:myabko
ID: 21757111
is there a possibility of getting the last created auto-incremented variable without relying on other data within the other fields within the table? while rare, there may be a case where the creation date and batch number are repeated with a different batch ID
0
 
LVL 24

Accepted Solution

by:
fridom earned 500 total points
ID: 21757172
insert into Record_Counts (Batch_ID_FK)
       select max(Batch_ID)

or the like, I guess MySQL has some fancier way to retrieve the last generated value.
Regards
Friedrich
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question