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

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?
myabkoAsked:
Who is Participating?
 
fridomConnect With a Mentor CEO/ProgrammerCommented:
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
 
fridomCEO/ProgrammerCommented:
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
 
myabkoAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.