How to insert data into tables related by foreign keys

[xtopher23]

Hi everybody! Well I'm building a web app using PHP and is connected to a MySQL database (I don't have much experience in web development). My db contains some tables that are related to each other by foreign key. However,when I try to insert data from a form into these tables, I get the following error:

Cannot add or update a child row: a foreign key constraint fails (`tradelink/client`, CONSTRAINT `client_ibfk_1` FOREIGN KEY (`acc_id`) REFERENCES `account` (`acc_id`) ON UPDATE CASCADE)

Here's part of my code..I don't know if it might help:
...
$sql_1="INSERT INTO client (f_name, l_name, tel) VALUES ('$Fname', '$Lname', '$Phone')";

$result_1 = mysql_query($sql_1);
if (!$result_1)
{
die('Sorry, could not query the database! result_1<br/>'.mysql_error());
}

$sql_2="INSERT INTO account (acc_type, email, password) VALUES ('$Acctype', '$Email', '$Pswrd')";

$result_2 = mysql_query($sql_2);
if (!$result_2)
{
die('Sorry, could not query the database! result_2<br/>'.mysql_error());
}

$sql_3="INSERT INTO companyprof (comp_name) VALUES ('$Cname')";

$result_3 = mysql_query($sql_3);
if (!$result_3)
{
die('Sorry, could not query the database! result_3<br/>'.mysql_error());
}
header('location:signup.php?reg_4=success');
...

As you can see,I've used 3 different queries to insert the data in the tables..there must certainly be an efficient way of doing that but unfortunately I don't know how :o

I'll be grateful if somebody could help me out

[TheBearMay]

Just looking at the error message and your first insert it appears that your error may be caused by not giving a value to acc_id on the client insert.

[chazzy]

looks like you need to insert the account first, then the client.

[xtopher23]

Hi guys! 1st of all I'd like to thank both of you for ur quick response :) And TheBearMay I'm sorry about posting the thread twice :o while I was posting it the 1st time, my connection got timed out and I thought the post wasn't successful.

Now both of your replies have helped me a lot. I've shifted the queries the other way i.e...I did the 1st insert in the companyprof table, then in the account table and lastly in the client table. The data for the 1st and 2nd queries were successfully inserted in there respective tables. However, I still get the same error msg for the insert in the client table :(

The client table has got client_id as PK, acc_id as FK and comp_id also as FK, if I'm right, whenever records are inserted in the parent tables i.e account & companyprof tables, the foreign keys should get inserted in the child table i.e client.

Am I wrong here? (I wasn't attentive in my RDBMS course LoL) Can anyone please tell me what's going wrong?

Thx

[chazzy]

maybe you should give us your full table definitions.

[xtopher23]

Hi Chazzy. So here are my table definitions.

CREATE TABLE `account` (
`acc_id` int(5) NOT NULL,
`acc_type` varchar(45) NOT NULL,
`status` varchar(10) NOT NULL,
`email` varchar(45) NOT NULL,
`password` varchar(32) NOT NULL,
`secret_question` mediumtext NOT NULL,
`secret_answer` mediumtext NOT NULL,
PRIMARY KEY (`acc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `companyprof` (
`comp_id` int(5) NOT NULL auto_increment,
`comp_name` varchar(45) NOT NULL,
`business_type` varchar(45) NOT NULL,
`prod_service` varchar(45) NOT NULL,
`comp_add` varchar(45) NOT NULL,
`comp_logo` varchar(45) NOT NULL,
`comp_desc` longtext NOT NULL,
`yr_established` varchar(45) NOT NULL,
PRIMARY KEY (`comp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `client` (
`client_id` int(5) NOT NULL auto_increment,
`acc_id` int(5) NOT NULL,
`comp_id` int(5) NOT NULL,
`f_name` varchar(45) NOT NULL,
`l_name` varchar(45) NOT NULL,
`gender` varchar(10) NOT NULL,
`address` varchar(45) NOT NULL,
`tel` varchar(7) NOT NULL,
PRIMARY KEY (`client_id`),
KEY `acc_id` (`acc_id`),
KEY `comp_id` (`comp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

And the constraints for the client table is as follows:

ADD CONSTRAINT `client_ibfk_1` FOREIGN KEY (`acc_id`) REFERENCES `account` (`acc_id`) ON UPDATE CASCADE,
ADD CONSTRAINT `client_ibfk_2` FOREIGN KEY (`comp_id`) REFERENCES `companyprof` (`comp_id`) ON UPDATE CASCADE;

Thank you very much for helping me :)

[chazzy]

your add constraints don't have originating tables.

[b4web]

Do you have indexes defined? Below is from MySQL 5.0 Reference:


"14.2.6.4. FOREIGN KEY Constraints
InnoDB also supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Foreign keys definitions are subject to the following conditions:

Both tables must be InnoDB tables and they must not be TEMPORARY tables.

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same.

In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length.

If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically. "

[xtopher23]

Hi everybody! Thx for the info b4web. But Chazzy what do you mean by originating tables? (0_o) :confused:

[chazzy]

sorry, meant parent.

parent is where the FK actually is.

[xtopher23]

Hi guys! I found a way to insert those data in their respective tables by setting the foreign keys to NULL. However this is not a solution because if the foreign key is allowed to be NULL then it no more serves it's purpose!! I won't be able to reference the values between the tables this way :(

Can anybody tell whether a foreign key in a child table, can automatically get the value it actually has in the parent table?

Also I thought of another way to insert the data in their respective tables with the FK set to NOT NULL but I wonder if a professional web developer would do it that way!?

Take a look at that and please let me know if there is a better way of get the values of the foreign key insert in the child table.

....
$sql_1="INSERT INTO client (f_name, l_name, tel) VALUES ('$Fname', '$Lname', '$Phone')";

$result_1 = mysql_query($sql_1);
if (!$result_1)
{
die('Sorry, could not query the database! result_1<br/>'.mysql_error());
}

$sqlx="SELECT client_id FROM client ORDER BY client_id DESC LIMIT 1";

$row = mysql_fetch_array($rslt);
$cl_fk = $row['client_id'];

$sql_2="INSERT INTO comprof (client_id, comp_name) VALUES ('$cl_fk', '$Cname')";

$result_2 = mysql_query($sql_2);
if (!$result_2)
{
die('Sorry, could not query the database! result_2<br/>'.mysql_error());
}

$sql_3="INSERT INTO account (client_id, acc_type, email, password) VALUES ('$cl_fk', '$Acctype', '$Email', '$Pswrd')";

$result_3 = mysql_query($sql_3);
if (!$result_3)
{
die('Sorry, could not query the database! result_3<br/>'.mysql_error());
}
....

Might look a lil bit silly :D...but anyway it helps

Rgds :)