?
Solved

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

Posted on 2008-06-10
3
Medium Priority
?
936 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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 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