如何修复错误'INSERT语句与FOREIGN KEY约束冲突' [英] How to fix error 'The INSERT statement conflicted with the FOREIGN KEY constraint'

问题描述

如果外键有值，则插入数据。如果没有显示错误

错误是

If foreign key has value, the data is inserting. if not it showing error
error is

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Team_Poli__Distr__63D8CE75". The conflict occurred in database "Teamindia7717", table "dbo.Team_District", column 'District_Id'.
The statement has been terminated.

我尝试设置外键值null和defalut值0但它仍然显示同样的错误



我的代码是

and i have tried setting foreign key value null and defalut value 0 but it showing still same error

my code is

protected void Button1_Click(object sender, EventArgs e)
       {
           try
           {

               con.Open();


               int State_ID = Convert.ToInt32(ddlState.SelectedValue);
               int District_ID = Convert.ToInt32(ddldistrict.SelectedValue);

               int Area_ID;
               int Village_ID;
               if (ddlArea.SelectedValue == "")
               {
                    Area_ID = 0;

               }
               else
               {
                   Area_ID = Convert.ToInt32(ddlArea.SelectedValue);
               }
               if (ddlVillage.SelectedValue == "")
               {
                    Village_ID = 0;

               }
               else
               {
                   Village_ID = Convert.ToInt32(ddlVillage.SelectedValue);
               }
               SqlCommand cmd = new SqlCommand();
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.CommandText = "SP_InsertPoliticalLeaders";
               cmd.Parameters.AddWithValue("@SelectPosition", ddlselectposition.SelectedItem.ToString());
               cmd.Parameters.AddWithValue("@State_Id", State_ID);
               cmd.Parameters.AddWithValue("@District_Id", District_ID);
               cmd.Parameters.AddWithValue("@Area_Id", Area_ID);
               cmd.Parameters.AddWithValue("@Village_Id", Village_ID);
               cmd.Parameters.AddWithValue("@IsPM", chkIsPM.Checked.ToString());

               cmd.Parameters.AddWithValue("@IsCM", chkIsCM.Checked.ToString());
               cmd.Parameters.AddWithValue("@IsCentralMinister", chkIsCentralMinister.Checked.ToString());
               cmd.Parameters.AddWithValue("@IsStateMinister", chkIsStateMinister.Checked.ToString());

               cmd.Parameters.AddWithValue("@IsCorporator", chkIsCorporator.Checked.ToString());
               cmd.Parameters.AddWithValue("@IsActive", chkIsActive.Checked.ToString());
               cmd.Parameters.AddWithValue("@PartyName", ddlPartyName.Text);
               cmd.Parameters.AddWithValue("@Name", txtName.Text);
               cmd.Parameters.AddWithValue("@Position", txtPosition.Text);
               cmd.Parameters.AddWithValue("@StartPeriod", txtStartPeriod.Text);
               cmd.Parameters.AddWithValue("@EndPeriod", txtEndPeriod.Text);
               cmd.Parameters.AddWithValue("@DOB", txtDob.Text);
               cmd.Parameters.AddWithValue("@PlaceOfbirth", txtPlaceOfBirth.Text);
               cmd.Parameters.AddWithValue("@Religion", ddlReligion.SelectedItem.ToString());
               cmd.Parameters.AddWithValue("@Education", txtEducation.Text);
               cmd.Parameters.AddWithValue("@PermenentAddress", txtpermanentAddress.Text);
               cmd.Parameters.AddWithValue("@OfficialAddress", txtOfficialAddress.Text);
               cmd.Parameters.AddWithValue("@MobileNumber", txtMobile.Text);
               cmd.Parameters.AddWithValue("@EmailId", txtEmail.Text);
               cmd.Parameters.AddWithValue("@Website", txtWebsite.Text);
               cmd.Parameters.AddWithValue("@Spouse", txtSpouse.Text);
               cmd.Parameters.AddWithValue("@Children", txtChildren.Text);
               cmd.Parameters.AddWithValue("@PoliticalCareer", txtPoliticalCareer.Text);
               cmd.Parameters.AddWithValue("@ImportantPositions", txtImportantPositions.Text);
               cmd.Parameters.AddWithValue("@Achievements", txtAchievements.Text);
               Guid FileName = Guid.NewGuid();
               ImageUpload.SaveAs(Server.MapPath("~/Images/" + FileName + ".png"));
               cmd.Parameters.AddWithValue("@ImagePath", "~/Images/" + FileName + ".png");

               cmd.Connection = con;

               cmd.ExecuteNonQuery();
               Label1.Text = "Inserted ";
           }
           catch (Exception ex)
           {
               throw ex;
           }
           finally
           {
               con.Close();
               con.Dispose();
           }

我的桌子是





创建表格Team_PoliticalLeaders

（

Id int主键标识（1,1），

SelectPosition nvarchar（100），

State_Id int外键引用Team_States（State_Id），

District_Id int外键引用Team_District（District_Id），

Area_Id int外键引用Team_Area（Area_Id），

Village_Id int外键引用Team_Village（Village_Id），

IsPM位，

IsCM位，

IsCentralMinister位，

IsStateMinister位，

IsCorporator位，

IsActive位，

PartyName varchar（250），

ImagePath nvarchar（最大值），

Name varchar（150），

Position varchar（500），

StartPeriod nvarchar（100），

EndPeriod nvarchar（100），

DOB nvarchar（150），

PlaceOfBirth varchar（250），
宗教varchar（50），

教育nvarchar（500），

PermenentAddress nvarchar（500），

OfficialAddress nvarchar（500），

MobileNumber nvarchar（15），

EmailId nvarchar（100），

网站nvarchar（500），

Spouse varchar（150），

儿童nvarchar（100），

PoliticalCareer nvarchar（max），

ImportantPositions nvarchar（ max），

成就nvarchar（最大），

）

My table is


create table Team_PoliticalLeaders
(
Id int primary key identity(1,1),
SelectPosition nvarchar(100),
State_Id int foreign key references Team_States(State_Id),
District_Id int foreign key references Team_District(District_Id),
Area_Id int foreign key references Team_Area(Area_Id),
Village_Id int foreign key references Team_Village(Village_Id),
IsPM bit,
IsCM bit,
IsCentralMinister bit,
IsStateMinister bit,
IsCorporator bit,
IsActive bit,
PartyName varchar(250),
ImagePath nvarchar(Max),
Name varchar(150),
Position varchar(500),
StartPeriod nvarchar(100),
EndPeriod nvarchar(100),
DOB nvarchar(150),
PlaceOfBirth varchar(250),
Religion varchar(50),
Education nvarchar(500),
PermenentAddress nvarchar(500),
OfficialAddress nvarchar(500),
MobileNumber nvarchar(15),
EmailId nvarchar(100),
Website nvarchar(500),
Spouse varchar(150),
Children nvarchar(100),
PoliticalCareer nvarchar(max),
ImportantPositions nvarchar(max),
Achievements nvarchar(max),
)

推荐答案

你在帖子中粘贴了什么与问题的观点无关。我们需要看到的是你调用的存储过程的DDL，当然还有受影响的表的DDL。



一般来说，如果指定外键约束你有两种方法：如果你在外键字段上允许null，你可以插入null，但如果不是，你需要在插入记录时在该字段中有有效的相关实体主键值。



一个常见问题是当这样的存储过程同时插入两个表时。比你需要先插入引用表，而不是引用引用表。如果引用表的主键是标识类型，则可以使用 @@ IDENTITY [ ^ ]并将其用于第二个插入语句。

What you have pasted in your post is irrelevant from the problem's point of view. What we need to see is the DDL of the stored procedure you call, and of course the DDL of the tables affected.

In general if you specify foreign key constraint you have two approaches: if you allow null on the foreign key field, you can insert null, but if you don't than you need to have in that field valid related entity primary key value when you insert the record.

A common problem is when such a stored procedure is inserting in both tables at the same time. Than you need first to insert into the referred table, than into the referring one. If the referred table's primary key is of identity type, you can retrieve the newly generated value with @@IDENTITY[^] and use it in the second insert statement.

您尝试插入重复的DistrictId，或者您没有传入数据库中已存在的有效区域的值。



对我来说，这听起来像是没有正确设置DistrictId上的外键关系。

Your trying to either insert a duplicate DistrictId or you're not passing in the value of a valid District that already exists in the database.

To me, this sounds like the foreign key relationship on DistrictId isn't setup correctly.

这篇关于如何修复错误'INSERT语句与FOREIGN KEY约束冲突'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！