CODEWITHSUNDEEP
mysqlinsertforeign-keys
Umur Kontacı
Umur Kontacı

Reputation: 35478

mysql inserting with foreign key

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?

Upvotes: 1

Views: 509

Answers (2)

bobflux
bobflux

Reputation: 11591

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

Upvotes: 1

Saher Ahwal
Saher Ahwal

Reputation: 9237

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?

Upvotes: 1

Related Questions