0

I am wanting to create a new user accounts on my web app while inserting data into multiple tables simultaneously in mysql. For example, I may have a table for general user information like authentification, while I may have another table that may have biography information for that user. A foreign key in the biography column must correspond to the primary key of the authentication table.

The user will fill out all that information in one form, but the information gathered will be placed in two seperate tables. The issue I am having is generating those tables in a way where those keys correspond, especially if I generate them at the same time.

Another example, I have a table with a list of restaurants called 'dining_listings'. One of those rows has a listing of 'All Foods Cuisine'. I may have a separate table called 'location_dining_types' listing all the food types that may correspond with that restaurant, with a foreign key corresponding to the primary key of that restaurant. There may be multiple rows in the 'locatiom_dining_types' table.

Also, I am wanting to generate a unique, primary key of various characters for each user. Instead of the standard auto_incrememt primary key, which by default starts at 1 and increments, I would like to generate a unique key of random characters, like fh4jnx68g4.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Hayden
  • 779
  • 10
  • 18

1 Answers1

1

You need LAST_INSERT_ID()

Try something like this:

 INSERT INTO user (given, surname) VALUES ('Michael', 'Widenius');
 SET @userId =: LAST_INSERT_ID();
 INSERT INTO company(user_id, name) VALUES (@userId, 'MySQL AB');
 INSERT INTO company(user_id, name) VALUES (@userId, 'MariaDB Corporation AB');

LAST_INSERT_ID gives you the autoincrementing ID value from, well, the last insert. You then save it in a user defined variable and then use it to populate the FK values in other tables.

This is safe even if you have a lot of programs doing inserts because the LAST_INSERT_ID value is maintained per database connection.

If you want to generate the value yourself, you can do this sort of thing.

 SET @userId = SELECT <<<random string>> 
 INSERT INTO user (user_id, given, surname) VALUES (@userId, 'Michael', 'Widenius');
 INSERT INTO company(user_id, name) VALUES (@userId, 'MySQL AB');
 INSERT INTO company(user_id, name) VALUES (@userId, 'MariaDB Corporation AB');

You want to know how to generate a random string in MySQL query language? Look here. Generating a random & unique 8 character string using MySQL

Caution: random strings as primary or foreign keys are harder to index efficiently than autoincrementing integers.

O. Jones
  • 103,626
  • 17
  • 118
  • 172