I have two different tables in a MySQL DB. I have successfully inserted data to to the parent table. However, I 'm having problems to the second table which has the FK from the first table.
I'm new to JDBC and haven't been able to find documentation on how to add data to two tables using the same insert method. I suppose I have to use two different preparedStatement within the same Insert method but I'm not really, plus it doesn't work.
Any ideas on how to solve this where I can research to find the solution.
No. It is ok to update more than one table in a class/connection. I usually close the prepared statement before opening another one, but I don't know that would matter.
I assume the table it updates is the first one. You mentioned there is a primary key between tables. Which field is it on? Because I don't see a field set in the second insert that looks like a primary key to the first.
Also, do you get any error messages? Does it throw an exception?
You have a column in your SQL called "account_type_account_name": are you sure about this? It looks like it should be two separate columns - ACCOUNT_TYPE and ACCOUNT_NAME. You should not merge columns in this way because you are making it hard to use these pieces of information individually.
Also, as Jeanne says, it's not clear what your PK is on the USER table. I recommend you use a surrogate key i.e. a numeric ID populated via MySQL's auto-increment feature. This USER_ID should then also be used as the foreign key in your ACCOUNTS table (assuming that's the FK relationship you want - again it's not clear what/where your keys are).
There are lots of good reasons for choosing a surrogate key with this kind of data e.g. it's guaranteed to be unique (you could have two users with the same name), it won't change (a user might change their name), it is independent of any changes to your business rules (because the business users don't care about it), it takes up less space, it's more efficient when searching, and it is much easier to use as a foreign key, especially when you have a tree of several table relationships.
@Chris - I used MySQL Workbench to create the DB and I have three tables that come into action for the problem that I'm presenting. The field account_type_account_name is the name of the 'account_name' in the table 'account_type'. Therefore the field is account_type_account_name is a FK in the account table (which is the one I'm trying to populate).
@Chris and @Jeanne - The User table PK is emailAddress, and the Account table PK is account ID which generated using java.util.UUID, and the account_type table PK is account_name. The account_name is FK in the account table and the emailAddress is FK in the account table. The account_type table already has information stored in it. For the moment the account_type table has only 3 Rows.
I have worked the insert method and I added some other fields and referenced the fields that were missing. This is how it looks right now, however the 2nd table is still no populated with data after the form was filled. It is not giving me any stacktrace.
subject: How to use preparedStatements to store data to two different tables