MySQL Auto-Increment Relationship

Question

So I'm writing a web app in vb.net and I've found myself a bit conceptually stumped with a particular database issue.

Essentially, I have 2 different "templates" for a form. In one, the user fills in a bunch of text fields and submits it, and it's all shipped off to the database. The second template is identical, except it tracks some additional information, so it submits more to the database. Rather than have a pair of tables with a lot of duplicate columns or a single table with a bunch of nulls, I made 1 table that tracks all the information shared by both templates and another table that stores all the "extra stuff" the 2nd template has.

The problem this has created is I need a way to pair the data from the two back together in order to search for the form and then pull the information out of the database. The collective forms are identified by a surrogate auto-incrementing key which is the primary key of the "shared" table. I attempted to set up a foreign key relationship with the "extra stuff" table, but doing so raised an issue on the application side where I'm not sure how to handle a foreign key that references an auto-increment in my insert statement.

To give a code example:

    Dim sInsertInto As String
    sInsertInto = "INSERT INTO 5why (date, op_id, serial, why1, why2, why3, why4, why5, root_cause, other_notes, lessons, define, template) VALUES (" + _
    "'" + f_date + "', " + _
    " '" + f_usr + "', " + _
    " '" + f_partnum + "', " + _
    " '" + f_first + "', " + _
    " '" + f_second + "', " + _
    " '" + f_third + "', " + _
    " '" + f_fourth + "', " + _
    " '" + f_fifth + "', " + _
    " '" + f_root + "', " + _
    " '" + f_notes + "', " + _
    " '" + f_lessons + "', " + _
    " '" + f_define + "', " + _
    " '" + f_temp + "'" + _
    ")"

    Dim sInsertInto2 As String
    sInsertInto2 = "INSERT INTO 5why_mbusi (countermeasure, containment, check_it, standardize_counter, point_cause, method_procedure, group_leader, engineer, shop_am, shop_manager) VALUES (" + _
    "'" + f_counter + "', " + _
    " '" + f_containment + "', " + _
    " '" + f_check + "', " + _
    " '" + f_standardCounter + "', " + _
    " '" + f_pointOfCause + "', " + _
    " '" + f_methodAndProc + "', " + _
    " '" + f_groupLeader + "', " + _
    " '" + f_engineer + "', " + _
    " '" + f_shop_A_M + "', " + _
    " '" + f_shopManager + ", '" + _
    ")"

In the first insert statement I'm inserting all the shared information into the "shared" table. I don't have to worry about the auto-increment here because it's all being handled by the database. The second insert statement ships all the extras into the "extra stuff" table, but I can't insert all those things without figuring out something to put into the foreign key, as it can't be null for the purposes of establishing a relationship between the two sets of data. I'm operating under the impression that just setting the foreign key to AI as well would just start it back over at "1," it wouldn't match the AI being generated by the "shared" table.

Any ideas out there on how to handle it? This was kind of tricky to word, so if you need clarification about anything, let me know and I'll do my best to clear it up.

Answer 1

The standard way to handle this is for the second table to not declare its primary key as auto-increment. Instead, you must specify the value of the primary key in your INSERT statement.

If you insert into the second table immediately after the first table, you can use the special function LAST_INSERT_ID() as the value.

Example:

INSERT INTO table1 (foo) VALUES (1234);  -- generates a new `id`

INSERT INTO table2 (id, bar) VALUES (LAST_INSERT_ID(), 'abcd');

The LAST_INSERT_ID() function returns the most recently generated auto-increment value by a prior INSERT statement in the same session. There is no chance that other people doing their own inserts in other sessions will compromise this.

PS: This is a separate issue from your original question, but FWIW you should learn to use query parameters instead of stringing together form fields with string concatenation. Using parameters is easier, faster, and more secure.