UPDATE foreign key using UPDATE query

Question

I'm trying to update a column, which is a foreign key, I'm trying to set it to a "NULL" value but the error says "ERROR 1452: Cannot add or update a child row: a foreign key constraint fails. "

try
 {  
    con.Open();
    MySqlCommand cmd = new MySqlCommand(
                "UPDATE tblcdsummary 
                    set PersonalInfoID = '" + DBNull.Value.ToString() + 
                    "' WHERE CDID = '" + looplabel2.Text + "'", con);


    cmd.ExecuteNonQuery();
    con.Close();
}
catch (Exception x)
{
    MessageBox.Show(x.Message);
}

**WARNING**: Do not inject raw strings into your queries. **Always** [properly escape](http://bobby-tables.com/csharp) any and all user data or you'll create awful [SQL injection bugs](http://bobby-tables.com/). — tadman, Jan 29 '17 at 18:26

score 2 · Answer 1 · answered Jan 29 '17 at 18:29

2

you need to first update value of this key in its actual table(ie. table reffered using this key)

answered Jan 29 '17 at 18:29

Yuva

131
1
6

you mean, I have to update the primary key to where it is referred to? – Ekko Jan 29 '17 at 18:34
yes you need to update primary key of reffered table as whatever value you give to foreign key will be checked that is that exist in primary key of reffered table? – Yuva Jan 29 '17 at 18:41

score 2 · Accepted Answer · edited May 23 '17 at 12:24

Do you know what is the result of DBNull.Value.ToString()?
It is an empty string. This means that if a relationship exists between these two tables then you are trying to relate the updated record to a record on the second table based on an empty string.
Of course, if the related table has no record with its primary key set to an empty string, your update will fail.

If you want to set that field to NULL then write it explicitly (no quotes)

MySqlCommand cmd = new MySqlCommand(
            @"UPDATE tblcdsummary 
                set PersonalInfoID = NULL 
                WHERE CDID = @id", con);
cmd.Parameters.Add("@id", MySqlDbType.VarChar).Value = looplabel2.Text;

Of course this works only if you allow NULL to be stored in PersonalInfoID column

PS. I have also changed your code to use a parameterized query. It is the only correct way to build sql command texts.
Don't concatenate strings. It is a really big error (Search for Sql Injection)

This actually HELPED, thank you so much, thank you for the small lesson. NOTED :) — Ekko, Jan 29 '17 at 18:48

UPDATE foreign key using UPDATE query

2 Answers2