Insert into multiple MySQL tables with one-to-one relationship

I have been asked if there is a way to insert data into two MyISAM tables that connected by a one-to-one relationship in one MySQL query.

For example:

mysql> CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
mysql> CREATE TABLE `user_details` (
  `id` int(10) unsigned NOT NULL,
  `weight` int(10) unsigned default NULL,
  `height` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)

How can I insert at once a new product with its details?
My answer is that you can’t insert into multiple MySQL tables in one query (if you know a way, I will be happy to learn;).
However, there are several simple and easy ways to do it:

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);

Using LAST_INSERT_ID in the insert value:

INSERT INTO user (name)
     VALUES ('John Smith');
INSERT INTO user_details (id, weight, height)
     VALUES (LAST_INSERT_ID(),83, 185);

Note: The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

Using PHP script:

// Connecting to database
$link = mysql_connect($wgScriptsDBServerIP, $wgScriptsDBServerUsername, $wgScriptsDBServerPassword, true);
if(!$link || !@mysql_SELECT_db($wgScriptsDBName, $link)) {
echo("Cant connect to server");
// 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 );

If you know of another good way please post a comment.

Note: This post was fixed according to the comments I have received from Singer Wang, Shlomi Noach and Chris Evers. Thanks guys!

MySQL Quiz
  1. Pascal says:

    I think, a good way will be to use stored procedure rather than separate queries 🙂

  2. Singer Wang says:

    Your argument doesn’t make sense. MyISAM and Foreign Keys?

  3. Anonymous says:

    In your example for the CREATE TABLE statement there was no FOREIGN KEY constraints made.

  4. Sorry, I have to throw in this little note: best not to discuss foreign keys while MyISAM examples.

    With your MyISAM tables, it is perfectly valid to first insert rows to `user_details`, then to `user`; whereas with foreign keys set in place that would be impossible.

    • Chris Evers says:

      I totally agree with Shlomi. Even more, I’d even that outside a transaction (not available with MyISAM, again), it could turn extremely dangerous for the consistency of data.

      The minimum would be to test the first result (in PHP), even if rollback isn’t available…

  5. Chris Evers says:

    … Even more, I’d add* that… (sorry)

  6. Ilan Hazan says:

    Thanks a lot friends.
    I have fixed my post.

  7. Octopus says:

    The Main Problem
    What if your first query executed successfully and the next one didn’t. Then a total chaos will happen. Products got inserted, but product customer is missing.

    There is no roll back for myisam tables. You will have to create your own roll backs.

  8. King Musa says:

    Guys I have the same problem but b4 that : that person should knw that the only Engine which supports foreign keys in mysql is InnoDB:
    bellow is my query and the second table has a foreign key which is on update from the first table but this query does not
    insert into the second table :
    it throws an error to me .
    INSERT INTO tish_user(username,Previllage)

    INSERT INTO tish_clientinfor(title,firstname)

  9. Nawaz says:

    Now if we have one-to-many or many-to-may relationhip than what can we do!!!!!!!!!!!! can we apply insert query for every one is there is any easy possible way to make it easy for one-to-many or many-to-many relation ship

  10. dalila says:

    i have problem, i cant insert foreign key in this exemple:
    create type site_type as object(idSite number, nomEcole VARCHAR (20) );

    create type ecole_type as object(idEcole number, nomEcole VARCHAR (20), adrEcole VARCHAR (30), refids ref site_type);

    create table site_obj of site_type( primary key(idSite));

    Create table ecole_tab OF ecole_type( CONSTRAINT pk_ecole PRIMARY KEY(idEcole),CONSTRAINT nn_refsta CHECK(refids IS NOT NULL),CONSTRAINT scope_refids refids SCOPE IS site_obj);
    help please

Leave a Reply