How to migrate database schema from Identity 2.2.0

2019-02-10 09:04发布

站内问答 / 后端开发
531 2 4

I've been playing around migrating an existing application running on MVC5 with ASP.NET Identity 2.2.0 having trouble finding proper information, i'd like to ask how you are supposed to do this migration?

I've used a couple of hours on this problem and have found a solution that allows me to log in with Identity 3.0.0-rc1-final with ef7 on my old but migrated database.

I will post what I did as an answer, but i'd very much like inspiration or other ways to have done it.

2条回答
祖国的老花朵
2楼-- · 2019-02-10 09:29

First I generated a new migration with the following command

dnx ef migration add MigrateIdentityFrameworkFrom2to3

This generated two files in the folder Migrations

  • XYZ_MigrateIdentityFrameworkFrom2to3.cs
  • ApplicationDbContextModelSnapshot.cs

The snapshot is the same as with ef6, a description of how your database looks. the other file is the actual migration containing the Up and Down commands for migrating your database.

The problems with migrating to the Identity 3.0.0-rc1-final schema appeared to be

  • AspNetRoles Two new database columns (ConcurrencyStamp, NormalizedName)
  • AspNetUsers 4 new columns (ConcurrencyStamp,LockoutEnd,NormalizedEmail,NormalizedUserName)
  • New Table (AspNetRoleClaims)

And in general the primary key on AspNetUsers and AspNetRole and Foreign Keys to theese tables had changed in length, from 128 to 450

The following is the Up and Down commands I used to be able to log in to my MVC6 application:

protected override void Up(MigrationBuilder migrationBuilder)
{
        migrationBuilder.DropForeignKey("FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId", "AspNetUserRoles");
        migrationBuilder.DropPrimaryKey("PK_dbo.AspNetRoles", "AspNetRoles");

        migrationBuilder.Sql(@"
        ALTER TABLE[AspNetRoles]
        ALTER COLUMN[Id] NVARCHAR(450) NOT NULL

        ALTER TABLE[AspNetUserRoles]
        ALTER COLUMN[RoleId] NVARCHAR(450) NOT NULL");

        migrationBuilder.AddPrimaryKey("PK_dbo.AspNetRoles", "AspNetRoles", "Id");
        migrationBuilder.AddForeignKey("FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId", "AspNetUserRoles", "RoleId", "AspNetRoles", principalColumn:"Id");

        migrationBuilder.DropForeignKey("FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId", "AspNetUserClaims");
        migrationBuilder.DropForeignKey("FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId", "AspNetUserLogins");
        migrationBuilder.DropForeignKey("FK_dbo.User_dbo.AspNetUsers_IdentityUser_Id", "User");
        migrationBuilder.DropForeignKey("FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId", "AspNetUserRoles");
        migrationBuilder.DropPrimaryKey("PK_dbo.AspNetUsers", "AspNetUsers");

        migrationBuilder.Sql(@"
        ALTER TABLE [AspNetUsers]
        ALTER COLUMN [Id] NVARCHAR(450) NOT NULL

        ALTER TABLE[AspNetUserRoles]
        ALTER COLUMN[UserId] NVARCHAR(450) NOT NULL

        ALTER TABLE[User]
        ALTER COLUMN[IdentityUser_Id] NVARCHAR(450) NOT NULL

        ALTER TABLE[AspNetUserLogins]
        ALTER COLUMN[UserId] NVARCHAR(450) NOT NULL

        ALTER TABLE[AspNetUserClaims]
        ALTER COLUMN[UserId] NVARCHAR(450) NOT NULL");

        migrationBuilder.AddPrimaryKey("PK_dbo.AspNetUsers", "AspNetUsers", "Id");
        migrationBuilder.AddForeignKey("FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId", "AspNetUserRoles", "UserId", "AspNetUsers", principalColumn: "Id");
        migrationBuilder.AddForeignKey("FK_dbo.User_dbo.AspNetUsers_IdentityUser_Id", "User", "IdentityUser_Id", "AspNetUsers", principalColumn: "Id");
        migrationBuilder.AddForeignKey("FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId", "AspNetUserLogins", "UserId", "AspNetUsers", principalColumn: "Id");
        migrationBuilder.AddForeignKey("FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId", "AspNetUserClaims", "UserId", "AspNetUsers", principalColumn: "Id");

        migrationBuilder.AddColumn<string>(name: "ConcurrencyStamp", table: "AspNetRoles", nullable: true);
        migrationBuilder.AddColumn<string>(name: "NormalizedName", table: "AspNetRoles", nullable: true);

        migrationBuilder.Sql(@"UPDATE AspNetRoles SET NormalizedName = UPPER(Name)");

        migrationBuilder.AddColumn<string>(name: "ConcurrencyStamp", table: "AspNetUsers", nullable: true);
        migrationBuilder.AddColumn<string>(name: "LockoutEnd", table: "AspNetUsers", nullable: true);
        migrationBuilder.AddColumn<string>(name: "NormalizedEmail", table: "AspNetUsers", nullable: true);
        migrationBuilder.AddColumn<string>(name: "NormalizedUserName", table: "AspNetUsers", nullable: true);
        migrationBuilder.Sql(@"UPDATE AspNetUsers SET NormalizedEmail = UPPER(Email), NormalizedUserName = UPPER(UserName)"); // MVC6 utilizes Email as login by default with forms authentication, and searches for the email in NormalizedUserName, I changed the login formular to utilize UserName instead of email when logging in, alternatively you can put in the email as NormalizedUserName.

        migrationBuilder.CreateTable(
            name: "AspNetRoleClaims",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                ClaimType = table.Column<string>(nullable: true),
                ClaimValue = table.Column<string>(nullable: true),
                RoleId = table.Column<string>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_IdentityRoleClaim<string>", x => x.Id);
                table.ForeignKey(
                    name: "FK_IdentityRoleClaim<string>_IdentityRole_RoleId",
                    column: x => x.RoleId,
                    principalTable: "AspNetRoles",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        migrationBuilder.AddColumn<string>(name: "ProviderDisplayName", table: "AspNetUserLogins", nullable: true);

        migrationBuilder.DropIndex(
            name: "RoleNameIndex",
            table: "AspNetRoles");
        migrationBuilder.CreateIndex(
            name: "RoleNameIndex",
            table: "AspNetRoles",
            column: "NormalizedName");

        migrationBuilder.CreateIndex(
            name: "EmailIndex",
            table: "AspNetUsers",
            column: "NormalizedEmail");

        migrationBuilder.DropIndex(
            name: "UserNameIndex",
            table: "AspNetUsers");
        migrationBuilder.CreateIndex(
            name: "UserNameIndex",
            table: "AspNetUsers",
            column: "NormalizedUserName");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey("FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId", "AspNetUserRoles");
        migrationBuilder.DropPrimaryKey("PK_dbo.AspNetRoles", "AspNetRoles");

        migrationBuilder.Sql(@"ALTER TABLE [AspNetRoles]
                               ALTER COLUMN [Id] NVARCHAR(128) NOT NULL
        ALTER TABLE[AspNetUserRoles]
        ALTER COLUMN[RoleId] NVARCHAR(128) NOT NULL");

        migrationBuilder.AddPrimaryKey("PK_dbo.AspNetRoles", "AspNetRoles", "Id");
        migrationBuilder.AddForeignKey("FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId", "AspNetUserRoles", "RoleId", "AspNetRoles", principalColumn: "Id");

        migrationBuilder.DropForeignKey("FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId", "AspNetUserClaims");
        migrationBuilder.DropForeignKey("FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId", "AspNetUserLogins");
        migrationBuilder.DropForeignKey("FK_dbo.User_dbo.AspNetUsers_IdentityUser_Id", "User");
        migrationBuilder.DropForeignKey("FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId", "AspNetUserRoles");
        migrationBuilder.DropPrimaryKey("PK_dbo.AspNetUsers", "AspNetUsers");

        migrationBuilder.Sql(@"ALTER TABLE [AspNetUsers]
                               ALTER COLUMN [Id] NVARCHAR(128) NOT NULL
        ALTER TABLE[AspNetUserRoles]
        ALTER COLUMN[UserId] NVARCHAR(128) NOT NULL

        ALTER TABLE[User]
        ALTER COLUMN[IdentityUser_Id] NVARCHAR(128) NOT NULL

        ALTER TABLE[AspNetUserLogins]
        ALTER COLUMN[UserId] NVARCHAR(128) NOT NULL

        ALTER TABLE[AspNetUserClaims]
        ALTER COLUMN[UserId] NVARCHAR(128) NOT NULL");


        migrationBuilder.AddPrimaryKey("PK_dbo.AspNetUsers", "AspNetUsers", "Id");
        migrationBuilder.AddForeignKey("FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId", "AspNetUserRoles", "UserId", "AspNetUsers", principalColumn: "Id");
        migrationBuilder.AddForeignKey("FK_dbo.User_dbo.AspNetUsers_IdentityUser_Id", "User", "IdentityUser_Id", "AspNetUsers", principalColumn: "Id");
        migrationBuilder.AddForeignKey("FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId", "AspNetUserLogins", "UserId", "AspNetUsers", principalColumn: "Id");
        migrationBuilder.AddForeignKey("FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId", "AspNetUserClaims", "UserId", "AspNetUsers", principalColumn: "Id");

        migrationBuilder.DropTable("AspNetRoleClaims");

        migrationBuilder.DropColumn(name: "ConcurrencyStamp", table: "AspNetRoles");
        migrationBuilder.DropColumn(name: "NormalizedName", table: "AspNetRoles");
        migrationBuilder.DropColumn(name: "ConcurrencyStamp", table: "AspNetUsers");
        migrationBuilder.DropColumn(name: "LockoutEnd", table: "AspNetUsers");
        migrationBuilder.DropColumn(name: "NormalizedEmail", table: "AspNetUsers");
        migrationBuilder.DropColumn(name: "NormalizedUserName", table: "AspNetUsers");
        migrationBuilder.DropColumn(name: "ProviderDisplayName", table: "AspNetUserLogins");


        migrationBuilder.DropIndex(
             name: "RoleNameIndex",
             table: "AspNetRoles");
        migrationBuilder.CreateIndex(
            name: "RoleNameIndex",
            table: "AspNetRoles",
            column: "Name");

        migrationBuilder.DropIndex(
            name: "EmailIndex",
            table: "AspNetUsers");

        migrationBuilder.DropIndex(
            name: "UserNameIndex",
            table: "AspNetUsers");
        migrationBuilder.CreateIndex(
            name: "UserNameIndex",
            table: "AspNetUsers",
            column: "UserName");
}
查看更多
0人赞 添加讨论(0) 举报
爱情/是我丢掉的垃圾
3楼-- · 2019-02-10 09:56

Here is an SQL script to convert Identity 2 database to Identity Core (or Identity 3 if you prefer). Notes:

This script was generated by the Visual Studio Schema Compare tool and then modified by hand. The goal of the script is to migrate the schema without dropping the AspNetUsers table. The other tables are dropped and recreated in the process and data is copied. If you have modifications to tables other than the users table or if you have modified the PK of the users table you should modify the script accordingly. There are several changes compared to the default schema related to PK sizes.

  • The users table has a PK of NVARCHAR(128) as it has in Identity 2 rather than NVARCHAR(450) what Identity 3 uses. All relationship in other tables are also changed to NVARCHAR(128)

  • The roles table has a PK of NVARCHAR(128). There were several warnings related to maximum key size exceeding 900 bytes for keys including the role id.

  • There are still some warning related to Login provider having longer keys but I think it is reasonable risk to let the ProviderKey be 450 characters as the actual external provider may use large keys. Note that the default ASP.NET Identity 3 schema has even larger primary keys.

I originally published the script here - https://github.com/Eirenarch/Identity2to3 I might modify this version if I need further changes or discover bugs.

PRINT N'Dropping [dbo].[AspNetUserClaims].[IX_UserId]...';
GO
DROP INDEX [IX_UserId] ON [dbo].[AspNetUserClaims];
GO

PRINT N'Dropping [dbo].[AspNetUserLogins].[IX_UserId]...';
GO
DROP INDEX [IX_UserId] ON [dbo].[AspNetUserLogins];
GO


PRINT N'Dropping [dbo].[AspNetUserRoles].[IX_RoleId]...';
GO
DROP INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles];
GO


PRINT N'Dropping [dbo].[AspNetUserRoles].[IX_UserId]...';
GO
DROP INDEX [IX_UserId] ON [dbo].[AspNetUserRoles];
GO


PRINT N'Dropping [dbo].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]...';
GO
ALTER TABLE [dbo].[AspNetUserRoles] DROP CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId];
GO


PRINT N'Dropping [dbo].[FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]...';
GO
ALTER TABLE [dbo].[AspNetUserClaims] DROP CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId];
GO


PRINT N'Dropping [dbo].[FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]...';
GO
ALTER TABLE [dbo].[AspNetUserLogins] DROP CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId];
GO


PRINT N'Dropping [dbo].[FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]...';
GO
ALTER TABLE [dbo].[AspNetUserRoles] DROP CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId];
GO


PRINT N'Starting rebuilding table [dbo].[AspNetRoles]...';
GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_AspNetRoles] (
    [Id]               NVARCHAR (128) NOT NULL,
    [ConcurrencyStamp] NVARCHAR (MAX) NULL,
    [Name]             NVARCHAR (256) NULL,
    [NormalizedName]   NVARCHAR (256) NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetRoles1] PRIMARY KEY CLUSTERED ([Id] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[AspNetRoles])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_AspNetRoles] ([Id], [Name], [NormalizedName])
        SELECT   [Id],
                 [Name],
                 UPPER([Name])
        FROM     [dbo].[AspNetRoles]
        ORDER BY [Id] ASC;
    END

DROP TABLE [dbo].[AspNetRoles];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetRoles]', N'AspNetRoles';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetRoles1]', N'PK_AspNetRoles', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Creating [dbo].[AspNetRoles].[RoleNameIndex]...';


GO
CREATE NONCLUSTERED INDEX [RoleNameIndex]
    ON [dbo].[AspNetRoles]([NormalizedName] ASC);


GO
PRINT N'Starting rebuilding table [dbo].[AspNetUserClaims]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_AspNetUserClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    [UserId]     NVARCHAR (128) NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetUserClaims1] PRIMARY KEY CLUSTERED ([Id] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[AspNetUserClaims])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_AspNetUserClaims] ON;
        INSERT INTO [dbo].[tmp_ms_xx_AspNetUserClaims] ([Id], [UserId], [ClaimType], [ClaimValue])
        SELECT   [Id],
                 [UserId],
                 [ClaimType],
                 [ClaimValue]
        FROM     [dbo].[AspNetUserClaims]
        ORDER BY [Id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_AspNetUserClaims] OFF;
    END

DROP TABLE [dbo].[AspNetUserClaims];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetUserClaims]', N'AspNetUserClaims';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetUserClaims1]', N'PK_AspNetUserClaims', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Creating [dbo].[AspNetUserClaims].[IX_AspNetUserClaims_UserId]...';


GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserClaims_UserId]
    ON [dbo].[AspNetUserClaims]([UserId] ASC);


GO
PRINT N'Starting rebuilding table [dbo].[AspNetUserLogins]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_AspNetUserLogins] (
    [LoginProvider]       NVARCHAR (128) NOT NULL,
    [ProviderKey]         NVARCHAR (450) NOT NULL,
    [ProviderDisplayName] NVARCHAR (MAX) NULL,
    [UserId]              NVARCHAR (128) NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetUserLogins1] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[AspNetUserLogins])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_AspNetUserLogins] ([LoginProvider], [ProviderKey], [ProviderDisplayName], [UserId])
        SELECT   [LoginProvider],
                 [ProviderKey],
                 [LoginProvider] AS [ProviderDisplayName],
                 [UserId]
        FROM     [dbo].[AspNetUserLogins]
        ORDER BY [LoginProvider] ASC, [ProviderKey] ASC;
    END

DROP TABLE [dbo].[AspNetUserLogins];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetUserLogins]', N'AspNetUserLogins';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetUserLogins1]', N'PK_AspNetUserLogins', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Creating [dbo].[AspNetUserLogins].[IX_AspNetUserLogins_UserId]...';


GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserLogins_UserId]
    ON [dbo].[AspNetUserLogins]([UserId] ASC);


GO
PRINT N'Starting rebuilding table [dbo].[AspNetUserRoles]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_AspNetUserRoles] (
    [UserId] NVARCHAR (128) NOT NULL,
    [RoleId] NVARCHAR (128) NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_AspNetUserRoles1] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[AspNetUserRoles])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_AspNetUserRoles] ([UserId], [RoleId])
        SELECT   [UserId],
                 [RoleId]
        FROM     [dbo].[AspNetUserRoles]
        ORDER BY [UserId] ASC, [RoleId] ASC;
    END

DROP TABLE [dbo].[AspNetUserRoles];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AspNetUserRoles]', N'AspNetUserRoles';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_AspNetUserRoles1]', N'PK_AspNetUserRoles', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Creating [dbo].[AspNetUserRoles].[IX_AspNetUserRoles_RoleId]...';


GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_RoleId]
    ON [dbo].[AspNetUserRoles]([RoleId] ASC);


GO
PRINT N'Creating [dbo].[AspNetUserRoles].[IX_AspNetUserRoles_UserId]...';


GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_UserId]
    ON [dbo].[AspNetUserRoles]([UserId] ASC);


GO

-- CHANGE THE REST OF THE SCRIPT! We should NOT drop AspNetUsers
PRINT N'Starting rebuilding table [dbo].[AspNetUsers]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

ALTER TABLE [dbo].[AspNetUsers]
ADD [ConcurrencyStamp] NVARCHAR(MAX) NULL
GO

ALTER TABLE [dbo].[AspNetUsers]
ADD [LockoutEnd] DATETIMEOFFSET(7) NULL
GO

ALTER TABLE [dbo].[AspNetUsers]
ADD [NormalizedEmail] NVARCHAR(256) NULL
GO

ALTER TABLE [dbo].[AspNetUsers]
ADD [NormalizedUserName] NVARCHAR(256) NULL
GO

DROP INDEX [UserNameIndex] ON [dbo].[AspNetUsers];
GO

UPDATE [dbo].[AspNetUsers]
SET [NormalizedEmail] = UPPER([Email]), [NormalizedUserName] = UPPER([UserName])
GO

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

PRINT N'Creating [dbo].[AspNetUsers].[EmailIndex]...';
GO
CREATE NONCLUSTERED INDEX [EmailIndex]
    ON [dbo].[AspNetUsers]([NormalizedEmail] ASC);
GO


PRINT N'Creating [dbo].[AspNetUsers].[UserNameIndex]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
    ON [dbo].[AspNetUsers]([NormalizedUserName] ASC);
GO


PRINT N'Creating [dbo].[AspNetRoleClaims]...';
GO
CREATE TABLE [dbo].[AspNetRoleClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    [RoleId]     NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO


PRINT N'Creating [dbo].[AspNetRoleClaims].[IX_AspNetRoleClaims_RoleId]...';
GO
CREATE NONCLUSTERED INDEX [IX_AspNetRoleClaims_RoleId]
    ON [dbo].[AspNetRoleClaims]([RoleId] ASC);
GO


PRINT N'Creating [dbo].[AspNetUserTokens]...';
GO
CREATE TABLE [dbo].[AspNetUserTokens] (
    [UserId]        NVARCHAR (128) NOT NULL,
    [LoginProvider] NVARCHAR (128) NOT NULL,
    [Name]          NVARCHAR (450) NOT NULL,
    [Value]         NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [Name] ASC)
);
GO


PRINT N'Creating [dbo].[FK_AspNetUserClaims_AspNetUsers_UserId]...';
GO
ALTER TABLE [dbo].[AspNetUserClaims] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;
GO

PRINT N'Creating [dbo].[FK_AspNetUserLogins_AspNetUsers_UserId]...';
GO


ALTER TABLE [dbo].[AspNetUserLogins] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;
GO


PRINT N'Creating [dbo].[FK_AspNetUserRoles_AspNetRoles_RoleId]...';
GO
ALTER TABLE [dbo].[AspNetUserRoles] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE;
GO


PRINT N'Creating [dbo].[FK_AspNetUserRoles_AspNetUsers_UserId]...';
GO
ALTER TABLE [dbo].[AspNetUserRoles] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;
GO


PRINT N'Creating [dbo].[FK_AspNetRoleClaims_AspNetRoles_RoleId]...';
GO
ALTER TABLE [dbo].[AspNetRoleClaims] WITH NOCHECK
    ADD CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE;
GO


PRINT N'Checking existing data against newly created constraints';
GO
ALTER TABLE [dbo].[AspNetUserClaims] WITH CHECK CHECK CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId];

ALTER TABLE [dbo].[AspNetUserLogins] WITH CHECK CHECK CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId];

ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK CHECK CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId];

ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK CHECK CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId];

ALTER TABLE [dbo].[AspNetRoleClaims] WITH CHECK CHECK CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId];


GO
PRINT N'Update complete.';
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)