• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 944
  • Last Modified:

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?
0
myabko
Asked:
myabko
  • 2
1 Solution
 
fridomCommented:
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
 
fridomCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now