Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - How to insert into multiple tables with foreign keys

Tags:

mysql

I'am new to MySQL, so please be nice :)

I would like to insert data from a php form into 3 different tables, which all have foreign keys. How can I write an insert command which updates all 3 tables at once, because if I try to update a table manually then I get an error because of the missing references. Do I have to deal with "NULL" entries and update every table after another or is it possible to solve this with one single command? Like MySQLi_Multi_Query?

Thank you very much!

like image 401
Crayl Avatar asked Dec 30 '10 18:12

Crayl


People also ask

Can a foreign key point to multiple tables?

A table can have multiple foreign keys based on the requirement.

How insert values into multiple tables in MySQL?

No, you can't insert into multiple tables in one MySQL command. You can however use transactions. BEGIN; INSERT INTO users (username, password) VALUES('test', 'test'); INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello world!

Can a foreign key reference multiple tables MySQL?

A foreign key constraint always references one target table. Polymorphic Associations are supported by frameworks such as Rails and Hibernate. But they explicitly say that you must disable SQL constraints to use this feature.

How do you insert data into a table that has a foreign key?

If you are inserting data into a dependent table with foreign keys: Each non-null value you insert into a foreign key column must be equal to some value in the corresponding parent key of the parent table. If any column in the foreign key is null, the entire foreign key is considered null.


2 Answers

You can do it in 3 Methods:

First & Recommended. USING SELECT IN THE INSERT VALUE:

   INSERT INTO user (name)
     VALUES ('John Smith');
INSERT INTO user_details (id, weight, height)
     VALUES ((SELECT id FROM user WHERE name='John Smith'), 83, 185);

Second. USING LAST_INSERT_ID IN THE INSERT VALUE:

INSERT INTO a (id)
     VALUES ('anything');
INSERT INTO user_details (id, weight, height)
     VALUES (LAST_INSERT_ID(),83, 185);

Third. USING PHP SCRIPT

<?php
// Connecting to database
$link = mysql_connect($wgScriptsDBServerIP, $wgScriptsDBServerUsername, $wgScriptsDBServerPassword, true);
if(!$link || !@mysql_SELECT_db($wgScriptsDBName, $link)) {
echo("Cant connect to server");
    exit;
}

// Values to insert
$name = 'John Smith';
$weight = 83;
$height = 185;

// insertion to user table
$sql = "INSERT INTO user (name) VALUES ('$name')";
$result = mysql_query( $sql,$conn );
// retrieve last id
$user_id = mysql_insert_id( $conn );
mysql_free_result( $result );

// insertion to user_details table
$sql = "INSERT INTO user_details (id, weight, height) VALUES ($user_id, $weight, $height)";
$result = mysql_query( $sql,$conn );
mysql_free_result( $result );
?>
like image 55
Meeshal Avatar answered Sep 18 '22 12:09

Meeshal


You're most likely going to have to insert things in order of their dependence. So if you have three tables (A, B, and C) we'll assume C depends on B and B depends on A. We'll also assume each table has primary keys AID, BID, and CID respectively.

  1. You'd insert your row into A and get AID.
  2. Then you'd insert your row into B using the AID you got from step 1.
  3. Then you'd insert your row into C using the BID (and perhaps AID) you got from step 2 (and perhaps 1)
like image 26
Corith Malin Avatar answered Sep 18 '22 12:09

Corith Malin