How do I insert data in two tables which are linked by foreign key?

Question

I have two tables which are linked by foreign key as follows.

userdetails
-----------------------------
uid|username|password|address
-----------------------------

phonedetails
-----------------------------
pid|uid|phone1|phone2
-----------------------------

uid is the foreign key in phonedetails table. I want to insert userdetails in userdetails table and their corresponding phone numbers in phonedetails table.

How can I do it?

http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization — Ianis, Dec 30 '15 at 15:32

score 0 · Answer 1 · edited May 23 '17 at 12:24

0

You can make procedure to insert data in both tables.

Procedure will be like this...

Insert userdetails data
SELECT @uid=SCOPE_IDENTITY() ; -- second step to catch the newly given uid of userdetail table in procedure.
Insert phonedetails data using the @uid variable.

More details here SQL - How to INSERT a foreign key as a value for a column

edited May 23 '17 at 12:24

Community

1
1

answered Dec 30 '15 at 15:31

Anil Panwar

2,592
1
11
24

score 0 · Accepted Answer · answered Dec 30 '15 at 15:39

0

Procedure :

DECLARE @uid INT
INSERT INTO dbo.userdetail
(Col1, Col2)
 VALUES (col1 value, col2 value)
 SELECT @uid =
 SCOPE_IDENTITY();
 INSERT INTO dbo.phonedetails
 (Col1,uid,col3) Values(col1 value,      @uid,col3 value)

answered Dec 30 '15 at 15:39

Anil Panwar

2,592
1
11
24

how do i do it using php? – girish Dec 30 '15 at 15:56
Refer http://stackoverflow.com/questions/3966747/how-to-call-a-mysql-stored-procedure-from-within-php-code – Anil Panwar Dec 30 '15 at 16:04
http://www.rndblog.com/insert-into-multiple-mysql-tables-linked-by-a-foreign-key/ – Anil Panwar Dec 30 '15 at 16:10

How do I insert data in two tables which are linked by foreign key?

2 Answers2