ASP.NET身份 - 如何将dbo.AspNetUsers.Id更改为非聚簇索引？ [英] ASP.NET Identity - How to change the dbo.AspNetUsers.Id into a nonclustered index?

问题描述

我正在使用ASPNET Identity 2.0，需要：

I'm using ASPNET Identity 2.0 and need to:

• 添加一个名为ApplicationUserId [int]的新字段聚集索引


• 将dbo.AspNetUsers.Id nvarchar字段从群集更改为非聚簇索引

在我的Models \\ IdentityModel.cs，我添加了：

In my Models\IdentityModel.cs, I added:

    public class ApplicationUser: IdentityUser
    {
            [Index(IsClustered = true, IsUnique = true)]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int ApplicationUserId { get; set; }
    }

当然，我知道这会出错，因为我知道Id目前一个聚集索引。
如何更改ID，以使默认值不是一个混合索引？

Of course, I knew this would error since I know that Id is currently a clustered index. How do I change the Id so that the default is not a clusered index?

基本上我想要的原因是我们的DBA不想使用外键是nvarchar（128）（这是AspNetUsers表中的GUID）。

Basically the reason I want to this is that our DBA does not want to use foreign keys that's a nvarchar(128) (which is this GUID from AspNetUsers table).

或者我只是将它创建为：

Or do I just create it as:

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ApplicationUserId { get; set; }

然后在创建表后手动修复索引？

and then manually fix the indexes after the tables are created?

或者是我唯一可以使用整数键而不是字符串？

Or is my only option to use integer keys instead of strings?

我正在使用.NET 4.5并安装了 Identity 2.0示例项目

I'm using .NET 4.5 and installed the Identity 2.0 Sample Project

 Install-Package Microsoft.AspNet.Identity.Samples -Pre

提前感谢您的帮助！

推荐答案

p>我得到它的工作，没有将GUID [nvarchar]转换为[int]

I got it to work without converting the GUID [nvarchar] to an [int]

基本上我更新了Models\IdentityModel.cs文件

Basically I updated the Models\IdentityModel.cs file

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ApplicationUserId { get; set; }

然后我手动更新了表（AspNetUserLogins，AspNetUserClaims和AspNetUserRoles引用了AspNetUsers.Id，我仍然想保持不变

Then I manually updated the tables (AspNetUserLogins, AspNetUserClaims, and AspNetUserRoles are referencing the AspNetUsers.Id which i still want to keep intact

begin tran
ALTER TABLE [dbo].[AspNetUserLogins] DROP CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
ALTER TABLE [dbo].[AspNetUserClaims] DROP CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]
ALTER TABLE [dbo].[AspNetUserRoles] DROP CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
ALTER TABLE dbo.[AspNetUsers] DROP CONSTRAINT [PK_dbo.AspNetUsers]
--Uncomment this line in case you need to modify this table again
--ALTER TABLE dbo.[AspNetUsers] DROP CONSTRAINT [dbo.AspNetUsers_ApplicationUserId]

-- Add the constraints back, but now nonclustered
ALTER TABLE dbo.[AspNetUsers] add constraint [PK_dbo.AspNetUsers] 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]

-- Add the other constraints back
ALTER TABLE [dbo].[AspNetUserLogins]  WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserClaims]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

-- Add NEW constraint with unique clustered
ALTER TABLE dbo.[AspNetUsers] add constraint [dbo.AspNetUsers_ApplicationUserId] unique clustered 
(
[ApplicationUserId]
)
commit tran

当您查看dbo.AspNetUsers的创建表脚本时，它将现在看起来像这样：

When you check out the create table script for dbo.AspNetUsers, it will now look like this:

CREATE TABLE [dbo].[AspNetUsers](
    [Id] [nvarchar](128) NOT NULL,
    [ApplicationUserId] [int] IDENTITY(1,1) NOT NULL,
    [Email] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [PhoneNumber] [nvarchar](max) NULL,
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEndDateUtc] [datetime] NULL,
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUsers] 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 [dbo.AspNetUsers_ApplicationUserId] UNIQUE CLUSTERED 
(
    [ApplicationUserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

这篇关于ASP.NET身份 - 如何将dbo.AspNetUsers.Id更改为非聚簇索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！