c# - 如何修复“违反 UNIQUE KEY 约束”
问题描述
我正在使用实体框架将用户添加到数据库。当我将第一个用户添加到数据库时,它可以工作。当我尝试将另一个用户添加到数据库时,出现错误:System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint
我不太确定我在这里做什么。我什至制作了一个 IF 条件来检查天气键是唯一的,但实际的错误消息说我正在插入一个重复的键。
所以这是我当前的代码:
//Create new GUID
string newGuid = Guid.NewGuid().ToString();
//Checking if Primary Key already exists in table
if (!context.Users_tbl.Any(u => u.UserID == newGuid))
{
//Installise New User Class
var user = new Users_tbl()
{
UserID = newGuid,
Email = email,
Password = AESCrypt.Encrypt(password),
};
//Add User to ADO and Commit Changes
context.Users_tbl.Add(user);
context.SaveChanges();
//Find UserID via Email
var userID = context.Users_tbl.Where(p => p.Email == email).Select(p => p.UserID).FirstOrDefault();
//Installise New UserInfo Class
var usersInfo = new UsersInfo_tbl()
{
FKUserID = userID,
FirstName = firstName,
LastName = lastName
};
//Add User to ADO and Commit Changes
context.UsersInfo_tbl.Add(usersInfo);
context.SaveChanges();
//Send Activation Link via Email
SendActivationEmail(userID);
//Set Session ID
Session["UserID"] = userID;
Response.Redirect("/Dashboard/DashBoard.aspx", false);
}
以下是我收到的确切错误消息。
System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UQ__Users_tb__2B5B96C51C3AA70E'. Cannot insert duplicate key in object 'dbo.Users_tbl'. The duplicate key value is (<NULL>).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
--- End of inner exception stack trace ---
at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut)
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Entity.Internal.InternalContext.SaveChanges()
--- End of inner exception stack trace ---
at System.Data.Entity.Internal.InternalContext.SaveChanges()
at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
at System.Data.Entity.DbContext.SaveChanges()
at ReapStreamV2.Auth.Signup.SignUp.Submit_Click(Object sender, EventArgs e) in C:\Users\Milan\Documents\ReapStream DEV\ReapStream\ReapStreamV2\Auth\Signup\SignUp.aspx.cs:line 123
我的主键是唯一的;
```
CREATE TABLE [dbo].[Users_tbl] (
[UserID] NVARCHAR (128) NOT NULL,
[Email] VARCHAR (255) NOT NULL,
[Password] VARCHAR (255) NOT NULL,
[IsTwoFAEnabled] BIT NOT NULL,
[TwoFACode] NVARCHAR (128) NULL,
[hasActivated] BIT NOT NULL,
[AccessFailedCount] INT NOT NULL,
CONSTRAINT [PK__Users_tb__1788CCAC29DEB237] PRIMARY KEY CLUSTERED ([UserID] ASC),
CONSTRAINT [UQ__Users_tb__2B5B96C51C3AA70E] UNIQUE NONCLUSTERED ([TwoFACode] ASC)
);
```
解决方案
错误消息指出“违反 UNIQUE KEY 约束 'UQ__Users_tb__2B5B96C51C3AA70E'。无法在对象 'dbo.Users_tbl' 中插入重复键。重复键值为 (<NULL>)。”
查看消息的下部,我看到 UQ__Users_tb__2B5B96C51C3AA70E 与 TwoFACode 字段相关。
在将用户添加到表的代码中,您没有填充此字段 - 因此违反了具有两个空条目的唯一约束。
“ [TwoFACode] NVARCHAR (128) NULL ” - 该字段允许为空 - 但由于约束,只能有一个空字段。
推荐阅读
- macos - 我在 Mac 机器上的 safari 浏览器上执行 Selenium 测试时遇到问题
- r - 句子文件的getDependency
- php - Laravel 日期搜索功能
- node.js - 在内存中保存许多 pubnub 实例时节点进程挂起
- gitlab - 如何在 Gitlab 合并请求讨论中获取新评论的通知
- r - R 中返回 NA 的 %in% 的优雅版本?
- ios - 在图库中上传图像时如何将图像路径作为参数发送
- android - 如何在 SharedPreference 中存储整数哈希集?
- .net - 使用更新 3 解决 asp.net mvc Visual Studio 2015 中的错误
- virtualbox - 如何使用 rhel 7.5 os 在虚拟框中安装 gcc make perl 包