如何防止重复 [英] How to prevent duplicates

问题描述

我有一个用于存储唯一电子邮件的SQL表.我需要一个存储过程，该存储过程可以使使用ASP.NET页插入数据的用户知道表中已经存在该电子邮件地址并指定一个新地址.
该表如下所示:

I have a SQL table for storing unique e-mails. I need a stored procedure that could let a user that uses ASP.NET page to insert data know that the e-mail address already exists on a table and specify a new one.
The table looks like this:

EMailID int NOT NUL (1,1),
UserID int NOT NULL FK,
EMailAdress varchar(128) NOT NULL,
Description varchar(100)

我的实际存储过程如下所示:

My actual stored procedure looks like this:

CREATE PROCEDURE dbo.InsertE-Mail
@UserID int,
@EMailAdress varchar(128),
@Description varchar(100)
AS
BEGIN
INSERT E-MAIL 
(
UserID,
EMailAdress,
Description
)
VALUES
(
@UserID,
@EMailAdress,
@Description
)
END
GO

任何人都可以帮忙吗?

Any one can help?

推荐答案

最简单的方法是在EmailAddress列上放置一个UNIQUE索引(注意，这与主键分开).这将导致insert语句失败，并显示一条相当智能的错误消息(密钥EmailAddress的重复条目"或类似内容)，您可以在顶层捕获该错误消息并为用户创建友好错误.

The simplest way would be to put a UNIQUE index (note, this is separate from the primary key) on the EmailAddress column. This will cause the insert statement to fail with a fairly intelligent error message (''duplicate entry for key EmailAddress'' or something similar) which you can catch in the top tier and create a friendly error for the user.

在插入数据之前，检查表中是否已存在电子邮件ID.

before inserting the data, check email id already exists in table or not..

INSERT INTO E-MAIL (UserID,EMailAdress,Description) SELECT UserID,@EMailAdress,@Description
WHERE NOT EXISTS (SELECT UserID  FROM E-MAIL WHERE EMailAdress = @EMailAdress)

或

Or

IF NOT EXISTS (SELECT UserID  FROM E-MAIL WHERE EMailAdress = @EMailAdress)
BEGIN
    INSERT INTO E-MAIL (UserID,EMailAdress,Description) VALUES (UserID,@EMailAdress,@Description)
END

这篇关于如何防止重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！