How can we join 2 tables and insert values into either one of the tables

Question

1.00/5 (1 vote)

See more:

I have 2 table Person table and an Address table and the address_id is given as foreign key in the Person table. Here i want to join these tables like whenever i try to insert the values to person i need to add address details also in the Address table for the same person and sore the Address_id in person table at the same time. The situation can be changed, like, first insert person details and after that on updating the profile only insert the Address details. How do i do these 2 conditions.

What I have tried:

I tried nothing so far. Confused because normally we give person_id in Address table as foreign key. But here its not like that.

Posted 14-Dec-22 20:58pm

Nikhil Mathew Franc

Updated 14-Dec-22 21:53pm

Add a Solution

Comments

Nikhil Mathew Franc 16-Dec-22 0:04am

But using spring data JPA my mentor added the relation like address_id in person Table as a foreign key and the address_id is auto_incremented. And wanted this address table to be updated only when updating the profile after log in function.

2 solutions

Solution 2

Quote:
Confused because normally we give person_id in Address table as foreign key. But here its not like that.

To add to what Richard (rightly) says...

Normally, you wouldn't have a person ID as a foreign key in the address table - because you normally have multiple people sharing the same address, not the other way round.

Having a Person as a foreign key means that you can have a person with multiple addresses, but you can't have multiple people with the same address. Since you would normally have a family (or a whole company) sharing a physical address rather than a single person being located in multiple places at once the address is a foreign key, and you add separate address columns to the person table for "home", "work", and so on as appropriate, or a separate table for "address type" and a linking table to join persons to addresses and types.

Posted 14-Dec-22 21:53pm

OriginalGriff

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2022-12-14T21:36:00

You can't. An INSERT can only insert into a single table.

You need to insert into the address table first. Then use LAST_INSERT_ID to get the ID of the inserted address (assuming you're using an auto-increment column for the ID). Then insert the details into the person table.

You'll want to wrap both insert statements in a transaction, to ensure they either both succeed, or both fail.
MySQL :: MySQL 8.0 Reference Manual :: 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements[^]