locked
one to one relationship - foreign key error RRS feed

  • Question

  • Hello :) I have a problem for a couple of weeks and I really don't know how to fix it. I have 2 tables 

    clienti(id_client(pk),nume, prenume, id_utilizator(fk)) and 
    utilizator(id_utilizator(pk), email, id_client(fk))

     When i'm trying to add a new record I keep getting "The UPDATE statement conflicted with the Foreign Key constraint "FK_client_utilizator etc". Does anybody know how to fix it? PLEASE it drives me nuts :(. 
            I've set for id_utilizator(fk) from clienti a DEFAULT VALUE that is found on the PK domain in utilizator. I can add client information, but of course id_utilizator is the DEFAULT VALUE. When I'm trying to update it,  I get that error. Here's my code:

    //adding information in client
    string tip_client = null;
                        string tip_persoana = null;
                        string judet = null;
                            if (tipClientCb.SelectedItem.ToString() == "Personal")
                                tip_client = "personal";
                            else
                                if (tipClientCb.SelectedItem.ToString() == "Altul")
                                    tip_client = "altul";


                            if (tipPersCb.SelectedItem.ToString() == "Persoana fizica")
                                tip_persoana = "persoana fizica";
                            else
                                if (tipPersCb.SelectedItem.ToString() == "Persoana juridica")
                                    tip_persoana = "persoana juridica";


                            judet = judetCb.SelectedItem.ToString();


                            string insertClient = "INSERT INTO client (tip_client,tip_persoana,cuicnp,denumire,telefon,localitate,judet) VALUES ('"
                                 + tip_client + "','" + tip_persoana + "','" + codClientTxt.Text + "','" + denumireTxt.Text + "','" + telefonTxt.Text + "','" + localitateTxt.Text+"','" + judet + "')";
                            System.Data.SqlClient.SqlCommand executaInsertClient = new System.Data.SqlClient.SqlCommand(insertClient, conn);
                            executaInsertClient.ExecuteNonQuery();
                        }
                           
                        //saving id_client
                        int id_client;
                        string selectIdClient = "SELECT * FROM client WHERE  tip_client='" + tipClientCb.Text +
                           "' AND tip_persoana='" + tipPersCb.Text +
                           "' AND cuicnp='" + codClientTxt.Text +
                           "' AND denumire='" + denumireTxt.Text +
                           "' AND localitate='" + localitateTxt.Text +
                           "' AND judet='" + judet + "'";
                        System.Data.SqlClient.SqlCommand executaSelectIdClient = new System.Data.SqlClient.SqlCommand(selectIdClient, conn);
                        System.Data.SqlClient.SqlDataReader rdr_id_client = executaSelectIdClient.ExecuteReader();
                        rdr_id_client.Read();
                        id_client = Convert.ToInt32(rdr_id_client["id_client"]);
                        rdr_id_client.Close();


                        //adding information in utilizator table
                        string insertUtilizator = "INSERT INTO utilizator (id_utilizator, id_client, parola,[e-mail]) VALUES ('"
                        + utilizatorTxt.Text + "','" + id_client + "','" + parolaTxt.Text + "','" + emailTxt.Text + "')";
                        System.Data.SqlClient.SqlCommand executaInsertUtilizator = new System.Data.SqlClient.SqlCommand(insertUtilizator, conn);
                        executaInsertUtilizator.ExecuteNonQuery();




                        //updating id_utilizator
                        string updateIdUtilizator = "UPDATE client SET id_utilizator='" + utilizatorTxt.Text + " WHERE id_client = " + id_client + "'";
                        System.Data.SqlClient.SqlCommand executaUpdateIdUtilizator = new System.Data.SqlClient.SqlCommand(updateIdUtilizator, conn);
                        executaUpdateIdUtilizator.ExecuteNonQuery();
    Sunday, March 31, 2013 9:35 AM

Answers

  • I believe the problem is that your table design could be better. You have your tables designed like this:

    clienti(id_client(pk),nume, prenume, id_utilizator(fk))
    utilizator(id_utilizator(pk), email, id_client(fk))

    Because you have both tables with FKs to each other, the only way you'll be able to insert a row into either one, is to allow NULLs in the both the PK and the FK columns AND each table needs to have a row with a NULL PK.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, March 31, 2013 2:30 PM

All replies

  • Your foreign key's value must be one of the values defined in your primary key.

    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats

    Sunday, March 31, 2013 9:49 AM
  • I believe the problem is that your table design could be better. You have your tables designed like this:

    clienti(id_client(pk),nume, prenume, id_utilizator(fk))
    utilizator(id_utilizator(pk), email, id_client(fk))

    Because you have both tables with FKs to each other, the only way you'll be able to insert a row into either one, is to allow NULLs in the both the PK and the FK columns AND each table needs to have a row with a NULL PK.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, March 31, 2013 2:30 PM