将外键id存储在另一个表中 [英] store foreign key ids in another table

问题描述

我有四张桌子M_Division



Divsion_ID（主键）部门



1 abc



2 xyz

M_Designation



Designation_ID（主键）名称


1位科学家



2位医生

M_emp个人



empid（主键）Divsion_ID（外键）Designation_ID（外键）emp_name



1 1 1 mike



2 2 2 justin

M_temp



empid（外键）Divsion_ID（外键） Designation_ID（外键）约会目的



1 1 1 12-06-2013 xyz


2 2 2 12-06-2013 abc



我想将外键ID插入M_temp表...如果我给出这样的查询，它会给我外键约束错误.....



插入M_temp（EmpID，DivisionID， DesigID，日期，目的，原因，created_date）选择（EmpID，DivisionID，来自M_emp_personal的DesigID，其中Empid = @ empid，conn）;



目的，日期是我的ASP.NET中的表单字段，它们需要与DivID DesigID和EmpID一起插入M_temp ...这就是我面临的问题......我不知道如何做到这一点可以帮助我吗？ br />


这是C＃代码

I have four tables M_Division

Divsion_ID(primary key) Division

1 abc

2 xyz
M_Designation

Designation_ID(primary key) Designation

1 scientist

2 doctor
M_emp personal

empid(primary key) Divsion_ID(foreign key) Designation_ID(foreign key) emp_name

1 1 1 mike

2 2 2 justin
M_temp

empid(foreign key) Divsion_ID(foreign key) Designation_ID(foreign key) date purpose

1 1 1 12-06-2013 xyz

2 2 2 12-06-2013 abc

I want to Insert the foreign key ID's into the M_temp table... If I give a query like this,it gives me the foreign key constraint error.....

Insert into M_temp(EmpID,DivisionID,DesigID,date,purpose,reason,created_date) Select(EmpID,DivisionID,DesigID from M_emp_personal where Empid=@empid",conn);

purpose,date are the form fields in my ASP.NET and they need to be inserted in the M_temp along with the DivID DesigID and EmpID...thats the problem I am facing...I dont know how exactly to do this can somebody help me out?

This is the C# code

protected void btnSubmit_Click2(object sender, EventArgs e)
       {
           string RelaseDate = Calendar1.SelectedDate.Date.ToString();

           SqlCommand cmd = new SqlCommand("Insert into T_TADA_tempform(EmpID,DivisionID,DesigID,date,purpose,reason,created_date) values(@EmpID,@DivisionID,@DesigID,@GPFNo,@date,@purpose,@reason), conn);
           cmd.Parameters.AddWithValue("@EmpID", ddlname.SelectedValue);
           cmd.Parameters.AddWithValue("@DivisionID", divisionID);
           cmd.Parameters.AddWithValue("@DesigID", lbldiv.Text);
           cmd.Parameters.AddWithValue("date", RelaseDate);
           cmd.Parameters.AddWithValue("@purpose", ddlpurpose.SelectedValue);
          




           if (conn.State == ConnectionState.Closed)
           {
               conn.Open();
               int cnt = cmd.ExecuteNonQuery();
               conn.Close();
               if (cnt == 1)
               {
                   Response.Redirect("");

               }
               else
                   Response.Write("Form has not been submitted,Please Try again!");
           }

       }

推荐答案

看来你正试图插入父表中没有的子行，因此违反了子父（即外键）约束。在插入之前，在其对应的表中检查empid（外键）Divsion_ID（外键）Designation_ID（外键）。

It seems that you are trying to insert the child row which doesn't have in your parent tables ,thus the child-parent (ie foreign key) constraint is violated . Check empid(foreign key) Divsion_ID(foreign key) Designation_ID(foreign key) in their corresponding tables before you insert.

这篇关于将外键id存储在另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！