How to insert in multiple table connected by foreign key, at same event

Question

I have two tables:

candidate_register_table with the following schema:

|----------------------------------------------------|
|   username  |  name   |  age   | sex   | password  |
|----------------------------------------------------|

candidate_social_table with the following schema

|--------------------------------------------------------|
|   username  |  religion   | caste  | address   | city  |
|--------------------------------------------------------|

In both tables, username are primary keys in their corresponding table.

I want to insert data in both the table in a single click event, where the username in table candidate_register_table is a foreign key in candidate_social_table also

This is my reg_conn.php where the data are getting inserted:

<?php
include('connection.php')'

$sql_register="INSERT INTO candidate_register_table (username, name, age, sex, password) )           VALUES('$_POST[username]','$_POST[name]','$_POST[age]', '$_POST[sex]','$_POST[password]')";
$sql_social_disable="ALTER TABLE candidate_social_table disable CONSTRAINT fk_candidate_register_table";
$sql_social="INSERT INTO candidate_social_table (username) VALUES('$_POST[username]')";
$sql_social_enable="ALTER TABLE candidate_social_table enable CONSTRAINT fk_candidate_register_table";

if(mysql_query($sql_register,$con) && mysql_query($sql_social_disable,$con) && mysql_query($sql_social,$con)  && mysql_query($sql_social_enable,$con))
   { echo "you have been registered";
   }
else
   {
       die('Error: ' . mysql_error());
       echo "Something went wrong. Might be a Fatal Error";
   }
?>

Now the problem is that, my connection and all others are all working perfectly, but the query:

ALTER TABLE candidate_social_table disable CONSTRAINT fk_candidate_register_table`

Is not getting executed, its throwing the following error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT fk_t1' at line 1

Before you do anything else today, read this http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — Mike B, Feb 26 '12 at 04:21

score 0 · Accepted Answer · answered Feb 26 '12 at 05:39

I'm not sure if you can disable a constraint for a single table, or why you need to disable the foreign key in the first place since you are inserting your data in the correct order - but you can try:

$sql_social_disable="SET FOREIGN_KEY_CHECKS=0";

to re-enable, use

$sql_social_disable="SET FOREIGN_KEY_CHECKS=1";

Alternatively, you can drop the specific foreign key constraint, then recreate it when you are done.

score 0 · Answer 2 · answered Feb 26 '12 at 08:42

0

It is not the right syntax to enable/disable keys. Try:

ALTER TABLE `candidate_social_table` DISABLE KEYS;

Alternatively you can SET FOREIGN_KEY_CHECKS = 0 before running the query. And reset to 1 post run.

But, I wonder why do you want to disable foreign keys when inserting into candidate_social_table. I mean the foreign keys have a purpose and disabling them defeats it. Remove the foreign key if you don't need it.

If efficiency is what concerns you, foreign keys are usually disabled when doing bulk inserts. Single or few inserts do not have any impact.

Another thing is if username is a primary key in both tables, there is no point in having 2 tables then. You can move all fields from candidate_social_table to candidate_register_table.

Hope the above makes sense!

answered Feb 26 '12 at 08:42

Abhay

6,545
2
22
17

i know buddy what you are trying to say, but u know, i was facing the problem, and it only got solved by disabling and enabling the foreign keys, i tried other means, but all of them failed..So it seemed to be the only soln to my problem....As far as u said about moving all fiels in a single table, actualy i am inserting registrational, social, personal, professional, contacts and general info about a user, and these have separate tables.Each table has 8+ attr, moreover i'm using different divs for all these sections.if i put all these infos in a single tab, then it will have 50+ attr – Saswat Feb 27 '12 at 05:27
50+ attributes is no problem at all. The benefit that you gain by putting all info in a single table is that you don't need joins. – Abhay Feb 27 '12 at 13:55

How to insert in multiple table connected by foreign key, at same event

2 Answers2