CODEWITHSUNDEEP
mysqldatabase
Damien-Amen
Damien-Amen

Reputation: 7502

Use Auto incremented unique id primary key as foreign key in another table

I have a table primary_details which has the the following fields

uid ---> primary key & Auto incremented & unique
name
age

I have another table cust_info with the following fields

uid ---> foreign key of primary key in primary_details table & not unique
score
comments

Now the question is when ever I insert a row in primary_details table it automatically inserts the value in uid column and it auto increments on all subsequent new rows which is perfect. However now I want the uid in cust_info table to also have the same uid value as in the primary_details table when ever I write a row in the primary_details table

For Eg:

In primary_details table if I insert the following row

uid    name   age   
1      Mark   25

Now if I insert a record in secondary table right after the above one it should also get the uid 1

I'm using Java to write the tables. In java I can easily generate a uid and insert it into the table but I wanna use the unique ID and auto increment feature in MySQL

Upvotes: 2

Views: 1813

Answers (1)

Cyril Gandon
Cyril Gandon

Reputation: 17058

  1. Insert the row in the parent table primary_details.

  2. Then, retrieve the generated uid with the method you want : How to get the insert ID in JDBC?

  3. Insert in the child table cust_info using the previously generated uid.

On a side note, your child table should have its own uid and a different field for the foreign key.

Example:

Customer
uid ---> primary key auto incremented
name
age

CustomerDetails
uid ---> primary key auto incremented
customer_id---> foreign key on Customer table
score
comments

Upvotes: 2

Related Questions