JamieB

Many thanks for your help Keith, however, I've tried removing the cascade and the error still occurs, just without the "ON UPDATE CASCADE" towards the end. Also where it said ibfk_2 it now says ibfk_7 - I did a bit of removing and reapplying foreign keys which I guess is leading to that? I tried using 2 tables (categories and categories2 - that's what you meant right?) which I thought had worked but still no luck. Again the problem still seems to lie in values with a space. E.g. (users.user_pref1 (Pref1) now references categories.cat_name) (users.user_pref2 (Pref2) now references categories2.cat_name) Pref1: Engineering change to Engineering Pref2: Engineering change to Engineering Works fine (I *think* this used to be a problem with one table). But Pref1: Customer Service change to Customer Service Pref2: Customer Service change to Customer Service Will not. And Pref1: Engineering change to Engineering Pref2: Customer Service change to Customer Service (The error on this one is with Pref2 so clearly the Engineering 'change' has worked, but the CS one is failing) Will not either. I know there are no changes there but there is every possibility a user would make amends to their profile but leave the preferred industries as they were which would cause the above issue.

Okay I've just checked and was right about that, but your first comment confused me? I've tried to work around this by simply creating a 'cat_name2' field in 'categories' so that two fields aren't ON UPDATE CASCADE-ing the same field (user_pref1 -> cat_name, user_pref2 -> cat_name2) - only that doesn't seem to have worked either. Finally found an edit button. I've also discovered even if user_pref1 and user_pref2 are different, if you 'save profile' and no change is made to either I get the error, and that is with them referencing different fields (cat_name and cat_name2 respectively). But I definitely need that ON UPDATE CASCADE in place to change preference values with industry values.

Okay, I could be making a pretty huge mistake here early on which I should probably get out of my system right now. When I have been making foreign keys, I had assumed that the referenced field (in this case, cat_name) was the parent. So if I was to edit the cat_name of a field (say from 'Administrative' to 'Admin & Office'), with ON UPDATE CASCADE; the users preference would automatically update from 'Administrative' to 'Admin & Office' (preferences need to exactly match cat_names as they are used in modules such as 'recommended jobs'). Have I had this the wrong way round?

Sorry to triple post. I think I've found out when the problem occurs, but still don't know how to sort it. It seems that the problem is triggered when: user_pref1 and user_pref2 are changed to the same value at the same time (Changing one, then "saving profile", then changing the other and saving again doesn't seem to do it, they must be done at the same time) AND The value that both fields use contains a space AND A user attempts to change ONE value from the two same values To escape the error you need to change BOTH values at the same time. So I'm guessing this must be something to do with the space? Which doesn't help me solve it but could be useful to anyone who may be able to. Any help would be massively appreciated. Here is the query which causes the error, which seems fine to me (and I'm guessing is or it wouldn't work most of the time) $userid = $_SESSION['user_id']; $sql = "UPDATE users SET user_firstname = '" . mysql_real_escape_string($_POST['user_firstname']) . "', user_lastname = '" . mysql_real_escape_string($_POST['user_lastname']) . "', user_email = '" . mysql_real_escape_string($_POST['user_email']) . "', user_location = '" . mysql_real_escape_string($_POST['user_location']) . "', user_phone = '" . mysql_real_escape_string($_POST['user_phone']) . "', user_pref1 = '" . mysql_real_escape_string($_POST['user_pref1']) . "', user_pref2 = '" . mysql_real_escape_string($_POST['user_pref2']) . "', user_profile = '" . mysql_real_escape_string($_POST['user_profile']) . "' WHERE user_id = $userid"; $result = mysql_query($sql) or die(mysql_error(). include 'footer.php');

UPPER CASE Capitalized I think?

You're missing a . after firstname .$row['firstname'] should be .$row['firstname'].

Apologies, my MySQL server version is 5.5.8 I haven't included any code as I don't think that's where the issue lies.

Hey guys, I'm working on trying to code a job site style CMS at the minute, and am sometimes getting the following error: "Cannot add or update a child row: a foreign key constraint fails (`eyr`.`users`, CONSTRAINT `users_ibfk_2` FOREIGN KEY (`user_pref1`) REFERENCES `categories` (`cat_name`) ON UPDATE CASCADE)1" 'eyr' = Database name 'users' = Table name 'user_pref1' = Field in 'users' (user_pref1 varchar(255) latin1_swedish_ci (Null?) Yes NULL ) 'categories' = Table name 'cat_name' = Field in 'categories' (cat_name varchar(255) latin1_swedish_ci (Null?) No None ) The issue also occurs with 'user_pref2' = Field in 'users' (user_pref2 varchar(255) latin1_swedish_ci (Null?) Yes NULL ) 'user_pref1' and 'user_pref2' both reference categories.cat_name, set to "ON UPDATE CASCADE" The infuriating thing is - the error only happens when it feels like it. Sometimes it will modify the table fine and other times it will give the error. Here's a quick summary of where the problem is happening: Users can register Registered users can have a profile visible to recruiters including "industry preferences" There are 2 industry preferences Both industry preferences must be set (there is no blank option in the drop down menu) When editing a user profile, changing either of the preferred industries will sometimes (but not always, and I cannot figure out what determines whether or not it does) trigger the error. The option is changed using a drop down menu which takes the list of industries from the database - so it isn't possible to enter 'incorrect' data. I have flushed the tables before when I was getting the error every time which I though had solved it but clearly not. Any ideas as to what would be triggering the error and how it could be prevented? Having just copied that field information maybe I'll see if it's anything to do with cat_name not being able to be null - but as user_pref1 and user_pref2 always have a value I don't know if this will be it. I stopped working on this for a good few weeks so am a bit out of touch with it and the things I've changed regarding the error but any extra information I'm sure I can provide. It usually seems to work fine the first few times after logging in - could it be anything to do with sessions or anything like that?