独特的键声音错误.... [英] Unique key Voilation error....

问题描述

大家好



请任何人帮助我...

我收到此错误。我正在开发一个Windows应用程序当我上传文件

第一次上传完美但当我上传同一个文件时上传失败并抛出我这个例外：

Hi all

Please can anyone help me...
I am getting this error.I am working on a windows application and when I am uploading the file
first time it uploads perfectly but when I upload the same file the upload fails and throws me this exception:


"

"Violation of UNIQUE KEY constraint 'UniqueVisit'.
Cannot insert duplicate key in object 'dbo.Visit\r\nThe statement has been terminated.
\r\nThe 'Visit_Insert'
procedure attempted to return a status of NULL, which is not allow...

请允许任何人就此问题向我提出建议但我无法解决问题。



这是我的桌子

Please can anyone advice me on this I tried but not able to fix the issue.

Here is my table

CREATE TABLE [dbo].[Visit](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[VerId] [bigint] NULL,
	[ClientId] [bigint] NOT NULL,
	[VisitDate] [datetime] NOT NULL,
	[VisitId] [char](10) NULL,
	[Child] [nvarchar](50) NULL,
	[CurrentMeth] [nvarchar](50) NULL,
	[FollUp] [nvarchar](50) NULL,
	[GestAge] [nvarchar](50) NULL,
	[MedicalRef] [nvarchar](50) NULL,
	[Sensitive] [nvarchar](50) NULL,
	[MarketingRef] [nvarchar](50) NULL,
	[Category] [nvarchar](50) NULL,
 CONSTRAINT [aaclientTransaction_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UniqueVisit] UNIQUE NONCLUSTERED 
(
	[ClientId] ASC,
	[VisitId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Visit]  WITH NOCHECK ADD  CONSTRAINT [FK_clientTransactions_Client] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Client] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Visit] NOCHECK CONSTRAINT [FK_clientTransactions_Client]
GO
ALTER TABLE [dbo].[Visit]  WITH NOCHECK ADD  CONSTRAINT [FK_ServicePointStatistic_Version] FOREIGN KEY([VersionId])
REFERENCES [dbo].[Version] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Visit] CHECK CONSTRAINT [FK_ServicePointStatistics_Version]

存储过程插入

Stored Procedure to insert

ALTER Procedure [dbo].[Visit_Insert]
	@VerId bigint, 
	@ClientId bigint, 
	@VisitDate datetime, 
	@VisitId char(10),
	@Child nvarchar(50),
	@CurrentMeth nvarchar(50),
	@FollUp nvarchar(50),
	@GestAge nvarchar(50),
	@MedicalRef nvarchar(50),
	@Sensitive nvarchar(50),
	@MarketingRef nvarchar(50),
	@Category nvarchar(50),
	@Id bigint OUTPUT
	
AS

insert into Visit  
					(VerId,
					ClientId,
					VisitDate,
					VisitId,
					Child,
					CurrentMeth,
					FollUp,
					GestAge,
					MedicalRef,
					Sensitive,
					MarketingRef,
					Category)
					values
					(@VerId,
					@ClientId,
					@VisitDate,
					@VisitId,
					@Child,
					@CurrentMeth,
					@FollUp,
					@GestAge,
					@MedicalRef,
					@Sensitive,
					@MarketingRef,
					@Category)
SET @Id = @@IDENTITY

RETURN @Id

谢谢

Thanks

推荐答案

您已使用语句 CONSTRAINT [UniqueVisit] UNIQUE NONCLUSTERED 创建了唯一非聚集索引。这会对您指定的索引键强制执行唯一约束。



您必须在这些列中插入重复值，这会产生此错误。

You have created a unique non-clustered index using the statement CONSTRAINT [UniqueVisit] UNIQUE NONCLUSTERED. This enforces a unique constraint on the index key you specified.

You must be inserting duplicate values in these columns which is giving this error.

问题不在于您的表结构或存储过程，但问题在于您正在使用的逻辑。

如果您想要SP到更新现有文件或删除并插入然后你应该改变你的逻辑。

你现在的SP正在做你设计的那样。



我建议你使用以下结构..



如果存在（条件）

开始

更新声明

结束

其他

开始

插入声明

end

和我是否需要删除唯一的非聚集索引我会说这完全取决于您的需要。如果您不需要列中的唯一数据，那么您可以删除它。

我同意Ankur ..阅读更多关于约束的内容..



http://www.w3schools.com/sql/sql_constraints.asp [ ^ ]



http://databases.about.com/od/sqlserver/a/constraints.htm [ ^ ]



http://odetocode.com/articles/79.aspx [ ^ ]

Hi,
Problem is not with your table structure or your stored procedure, but problem is in the logic you are using.
If you want your SP to update the existing file or delete and insert then you should change your logic.
your present SP is doing what you have designed it to do.

I would suggest you to use following structure..

if exist(condition)
begin
update statements
end
else
begin
insert statement
end
and about "Do I need to remove the unique non clustered index" I would say that it totally depends on your need. If you don't need unique data in column then you can remove it.
I agree with Ankur.. read little more about constraints..

http://www.w3schools.com/sql/sql_constraints.asp[^]

http://databases.about.com/od/sqlserver/a/constraints.htm[^]

http://odetocode.com/articles/79.aspx[^]

这篇关于独特的键声音错误....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！