2

I have three tables in my database. A users table, StoreA and StoreB

StoreA and StoreB both have a unique key which is the user ID value.

What I want is; When I create a user and insert them into the database, how can I Insert a row into the other two tables without too many additional queries.

I figure I can do this by inserting the user in one query, then in another return the newly created user ID, then in another, using said ID, create rows in StoreA and StoreB

Can I cut out the middle query?

Improve this question
3
  • What is the programming language? Commented Sep 9, 2011 at 15:05
  • 1
    are the User ID's an auto increment int? Commented Sep 9, 2011 at 15:06
  • Yes the user ID's are auto incrementing int. Commented Sep 9, 2011 at 15:17

5 Answers 5

Reset to default
7

Can I cut out the middle query?

YES

START TRANSACTION; INSERT INTO user (id, name, other) VALUES (null, 'John','rest of data'); INSERT INTO storeA (id, user_id, other) VALUES (null, @user_id:= LAST_INSERT_ID(), 'rest of data'); INSERT INTO storeB (id, user_id, other) VALUES (null, @user_id, 'rest of data'); COMMIT;

See: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

It's a good idea to do this in a transaction, you you're not stuck with just a user with no other data if something goes wrong.
It's not a DB requirement though.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

perhaps unrelated, but you may need to remember to use autocommit = 0 depending on your db type
what if you are using multiple rows insert at the same time ?
@CME64 I think it won't work in that case; did you manage to find a solution?
2

Yes - there should be a function available to get the last inserted ID (assuming it's an autoincrement field) without another query. In PHP, it's mysql_insert_id(). Just call that after the first query.

Improve this answer

Comments

0

YES

 Q1: insert into table1 values (...); Q2: insert into table2 values (last_insert_id(), ...);

last_insert_id is the default mysql build-in function

Most of the mysql libraries in various programming language did support return last insert id.
But You did not mention what sort of language you are using to connect to mysql.,
so cannot provide any example

Improve this answer

Comments

0

I just wanted to share a php solution.

If you're using mysqli, first execute your insert query. Then do

$db_id = $this->db->insert_id;

Improve this answer

Comments

-1

Why don't you use their username as the primary key instead of creating an arbitrary user_id field thats auto incremented? Their user names are unique, right?

Improve this answer

4 Comments

That's not a good idea. It's going to be more resource hungry to have a varchar/char as a foreign key (no matter if it's unique) vs. an integer.
Thats how you normalize databases I thought? BCNF? db.grussell.org/section009.html
@Stephen, its not a question of normalization, its a question of performance, nothing can beat integer keys for speed.
@Stephen Granet: Nope, a username is not a candidate key, because although it is required to be unique, it might be changeable. Just like an email is unique, but the need arises for it to change.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.