Last post Dec 21, 2011 07:30 PM by ChayaCooper
Dec 20, 2011 05:39 PM|ChayaCooper|LINK
My website and database work fine in WebMatrix until I add a Foreign Key, upon which I get the error message listed below. I created the connections in MySql Workbench, so I know that the problem isn't human error on this one.
Is there something I need to be doing differently in order to create the relationships between tables when working with mySql in WebMatrix and/or .Net?
Server Error in '/' Application.
Cannot add or update a child row: a foreign key constraint fails (`c2f_122011`.`flatter1`, CONSTRAINT `fk_flatter1_customer_info11` FOREIGN KEY (`customer_info1_user_id`) REFERENCES `customer_info1` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
Dec 20, 2011 11:18 PM|sunilgurjar|LINK
first of all u remove all the record and then add foreign key this will work
this error occur beacase of data dose not match like primary key and foreign key value.
Dec 21, 2011 01:33 AM|ChayaCooper|LINK
I've already removed all the records, so unfortunately that's not the problem
Dec 21, 2011 08:08 AM|Dan Bracuk|LINK
It sounds like you are attempting to write to one table without ensuring that a matching record exists in the foreign key table first. There are lots of ways to deal with this. The best one for you depends on the needs of your application.
Dec 21, 2011 10:38 AM|ChayaCooper|LINK
I am creating in-depth customer profiles with the main table (customer_info1) being where the customer account and general information is stored, and it connects to several tables which each have specific types of customer information (i.e. 'fit' has their
measurements and other information that relates to garment fit). Each table's information has to connect to user_id on the customer_info1 table in order for me to pull up information by customer.
Each table generates an auto-increment id (which is working properly), and when I've tested it I've gone to the main page first in order to create the user_id which the other tables all refer to.
The specific settings (in MySqlWorkbench) for table 'fit1' are:
fit_id - INT(11), Primary Key, Not Null, Unique Index, Auto Incremental
customer_info1_user_id - INT(11), Primary Key, Not Null, Auto Incremental
Primary -Type = Primary; Index Columns=fit_id and customer_info1_user_id; #=1 and 2
idstyle_UNIQUE - Type=Unique; Index Columns=fit_id; #=1
fk_fit1_customer_info1 - Type = Index; Index Columns=customer_info1_user_id; #=1
fk_fit1_customer_info11 - Referenced Table 'c2f_122011'.'customer_info1'; Column=customer_info1_user_id; Referenced Column=user_id
Dec 21, 2011 12:21 PM|Dan Bracuk|LINK
Dec 21, 2011 02:21 PM|ChayaCooper|LINK
I was under the impresssion that the Foreigh Key fk_fit1_customer_info11 created the connection between customer_info1_user_id in the fit1 table and the user_id in the customer_info1 table.
PRIMARY KEY (`fit_id`, `customer_info1_user_id`) ,
UNIQUE INDEX `customer_id_UNIQUE` (`fit_id` ASC) ,
INDEX `fk_fit1_customer_info1` (`customer_info1_user_id` ASC) ,
FOREIGN KEY (`customer_info1_user_id` )
REFERENCES `c2f_122011`.`customer_info1` (`user_id` )
ON DELETE CASCADE
ON UPDATE CASCADE
I've also tried setting ON DELETE and ON UPDATE to NO ACTION, but I still got the same error message.
Is there something else that I'm supposed to be doing?
Dec 21, 2011 02:47 PM|Dan Bracuk|LINK
The foreign key creates a constraint in that values in the fit1 table have to exist in the customer_user1 table.
It does not do anything to get a value from the customer_user1 table that you can use in an insert or update query. You have to do that yourself.
Dec 21, 2011 07:30 PM|ChayaCooper|LINK
No wonder it isn't working :-)
Since reading your posting I've been trying to figure out how to set up
but I'm having a tough time figuring out how to apply it to my code to get it to work because most of the instructions I've found are for PHP or are too technical in nature :-(
Would you be able to tell me how to do it for the code snippet below?
var SQLINSERT = "INSERT INTO fit1 (clothingsize) VALUES (@0)";
var db = Database.Open("Demo1");