将数据插入sql时发生冲突 [英] confliction when inserting data to sql

问题描述

我有三个桌子

1-第一个是父表并具有这些列

I have three table

1-first one is the parent table and have those columns

AdvId    AdvName   RegId

2秒是子表

2-second one is child table

RegId RegName   GovId 

在第一张和第二张桌子之间有一个FK
三分之三也是子表，但到第二个表

and there''s A FK between the first and the second table
3-third one is child table also but to the second table

GovId GovName 

在第二个和第三个表之间有一个外键

现在我有这个sql声明来声明数据

and there''s aforign key between the 2nd and 3rd table

NOW I have this sql statenment to isert data

ALTER proc [dbo].[InsertUser]
(@AdvName nvarchar(50),
@AddGov int,
@Addtype int,
@Addreg int,
@AddFloor int,
@Addfinish int,
@Addsalary int,
@Addtel int)
 
 as insert into AdvertiseNames
 (AdvertiseName,EstateId,FinishingId,FloorId,RegionId,Salary,Telephone)
 values
 (@AdvName,@Addtype,@AddFloor,@Addfinish,@Addsalary,@Addtel,@Addreg)
 
insert into Regions
(GovNo)
values
(@AddGov)

现在，我从下拉列表中插入regid和govid，并从文本框中插入advname

我有这个错误

Now I insert the regid and the govid from dropdownlist and advname from textbox

I have this Error

 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AdvertiseName_Regions". The conflict occurred in database "RealEstateSite", table "dbo.Regions", column 'RegionsId'.
Cannot insert the value NULL into column 'RegionName', table 'RealEstateSite.dbo.Regions'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The statement has been terminated.

>>我应该怎么做?????

>>>what shoul I do ?????

推荐答案

Yuo在表之间检查表中是否存在AdvertiseName.RegionsId值的外键.使用您提供的参数不存在.

因此，请检查您的参数@Addreg
中的值
另外，还要检查是否已在每个插入框中添加了所有必需的列和值.

加法:
看来插入顺序错误，先插入主表，再插入子表.
此外，值看起来与列的顺序不同.插入语句应类似于:

Yuo have a foreign key between the tables checking that AdvertiseName.RegionsId value is found in regions. With the parameters you have supplied this does not exist.

So check the value in your parameter @Addreg

Also check that you have supllied all necessary columns and values in each insert.

Addition:
It looks like the inserts are in wrong order, first insert to master table then to child.
Also the values look that they are in different order than the columns. Should the insert statement be like:

ALTER proc [dbo].[InsertUser]
(@AdvName nvarchar(50),
@AddGov int,
@Addtype int,
@Addreg int,
@AddFloor int,
@Addfinish int,
@Addsalary int,
@Addtel int)
 
 as 
insert into Regions
(GovNo)
values
(@AddGov);

insert into AdvertiseNames
 (AdvertiseName,EstateId,FinishingId,FloorId,RegionId,Salary,Telephone)
 values
 (@AdvName,@Addtype,@Addfinish,@AddFloor,@Addreg,@Addsalary,@Addtel); 

@Addtype中的值是否真的应该转到字段EstateId?

还有一件事.如果您每次在区域中添加一行，那不会导致重复吗?您应该仅在不存在区域时添加该区域吗?

Also is the value in @Addtype really supposed to go to field EstateId?

One more thing. If you every time add a row to regions, won''t that lead to duplicates? Should you add the region only if it does not exist?

首先，您写了一些丢失的内容

first you have written some missing

insert into regions
(GovId)
values
(@Addgov)

在此查询中也插入regionname.您已将此列设置为非空.并且您正在插入空值，因此出现错误.
尝试一下，如果不能解决，请立即将错误提示给我.

insert regionname also in this query. you have set this column not null. and you are inserting null value, so there is an error.
Try this, if not solve then send me what error comes now.

这篇关于将数据插入sql时发生冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！