mysql inserting with foreign key

Question

i do have a form field which includes values which will be put in different tables in mysql.

they are all connected with foreign keys.

how do i put these values to different tables.

pseudo tables:

users_table: userId|userlogin user_info: info_id|userId|name|surname user_contact: contact_id|userId|phone|email form includes: userlogin name surname phone email

in my research, i found out that i can use mysql_insert_id to link the FKs, but i wonder if that can cause problems if there is high load in the website (diff. requests sent at the same time).

i also found out that i can set triggers to create new fk values:

CREATE TRIGGER ins_kimlik AFTER INSERT ON hastalar for each row insert into hasta_kimlik set idhasta = new.idhasta

but i don't know how to add data to them. i can use
UPDATE table SET (name, surname) VALUES ('John', 'Brown') WHERE info_id = LAST_INSERT_ID();

but it doesn't feel the native way.

what is the best practise?

bobflux · Accepted Answer · 2011-07-26 19:55:20Z

i found out that i can use mysql_insert_id to link the FKs, but i wonder if that can cause problems if there is high load in the website (diff. requests sent at the same time).

mysql_insert_id returns the last auto-increment value generated by the database connection currently in use.

It doesn't matter what other processes do on other connections. It is safe. You'll get the right value.

but it doesn't feel the native way.

nope. The right way is :

INSERT user
get id
INSERT user_info

one more thing, i wonder the purpose of using a trigger then, is it useful or is there a way to make it useful for this case. maybe for many-to-many relationships but else, i don't know...
In this particular case the trigger on users doesn't know what you want to insert in the user_info table so it is not useful at all. Better stay simple...

Saher Ahwal · Accepted Answer · 2011-07-26 18:59:56Z

If the tables are connected by foreign keys shouldn't you just start with the basic table (users_table here) and then add in either user_info table and then in user_contact table, or the other way around. As long as you have filled in the table that has the primary key of the fk's in the other tables, then you can add easily.

INSERT SQL command:

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Is that what you were asking?

Collectives™ on Stack Overflow

mysql inserting with foreign key

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related