Click here to Skip to main content
15,879,864 members
Please Sign up or sign in to vote.
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
Updated 14-Dec-22 21:53pm
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.

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[^]
 
Share this answer
 
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.
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900