ASP.NET Core 2.1 Identity SQL Tables https://aaron-hoffman.blogspot.com/2018/06/aspnet-core-21-identity.html

aspnet-core-2-1-identity.sql

create table [dbo].[AspNetRoleClaims] (
    [Id] int IDENTITY(1, 1) not null,
    [RoleId] nvarchar(450) not null,
    [ClaimType] nvarchar(MAX) null,
    [ClaimValue] nvarchar(MAX) null,
    constraint [PK_AspNetRoleClaims] primary key clustered ([Id] asc)
    );
go

create nonclustered index [IX_AspNetRoleClaims_RoleId] on [dbo].[AspNetRoleClaims] ([RoleId] asc);
go


create table [dbo].[AspNetRoles] (
    [Id] nvarchar(450) not null,
    [Name] nvarchar(256) null,
    [NormalizedName] nvarchar(256) null,
    [ConcurrencyStamp] nvarchar(MAX) null,
    constraint [PK_AspNetRoles] primary key clustered ([Id] asc)
    );
go

create unique nonclustered index [RoleNameIndex] on [dbo].[AspNetRoles] ([NormalizedName] asc) where ([NormalizedName] is not null);
go


create table [dbo].[AspNetUserClaims] (
    [Id] int IDENTITY(1, 1) not null,
    [UserId] nvarchar(450) not null,
    [ClaimType] nvarchar(MAX) null,
    [ClaimValue] nvarchar(MAX) null,
    constraint [PK_AspNetUserClaims] primary key clustered ([Id] asc)
    );
go

create nonclustered index [IX_AspNetUserClaims_UserId] on [dbo].[AspNetUserClaims] ([UserId] asc);
go


create table [dbo].[AspNetUserLogins] (
    [LoginProvider] nvarchar(128) not null,
    [ProviderKey] nvarchar(128) not null,
    [ProviderDisplayName] nvarchar(MAX) null,
    [UserId] nvarchar(450) not null,
    constraint [PK_AspNetUserLogins] primary key clustered (
        [LoginProvider] asc,
        [ProviderKey] asc
        )
    );
go

create nonclustered index [IX_AspNetUserLogins_UserId] on [dbo].[AspNetUserLogins] ([UserId] asc);
go


create table [dbo].[AspNetUserRoles] (
    [UserId] nvarchar(450) not null,
    [RoleId] nvarchar(450) not null,
    constraint [PK_AspNetUserRoles] primary key clustered (
        [UserId] asc,
        [RoleId] asc
        )
    );
go

create nonclustered index [IX_AspNetUserRoles_RoleId] on [dbo].[AspNetUserRoles] ([RoleId] asc);
go


create table [dbo].[AspNetUsers] (
    [Id] nvarchar(450) not null,
    [UserName] nvarchar(256) null,
    [NormalizedUserName] nvarchar(256) null,
    [Email] nvarchar(256) null,
    [NormalizedEmail] nvarchar(256) null,
    [EmailConfirmed] bit not null,
    [PasswordHash] nvarchar(MAX) null,
    [SecurityStamp] nvarchar(MAX) null,
    [ConcurrencyStamp] nvarchar(MAX) null,
    [PhoneNumber] nvarchar(MAX) null,
    [PhoneNumberConfirmed] bit not null,
    [TwoFactorEnabled] bit not null,
    [LockoutEnd] datetimeoffset(7) null,
    [LockoutEnabled] bit not null,
    [AccessFailedCount] int not null,
    constraint [PK_AspNetUsers] primary key clustered ([Id] asc)
    );
go

create nonclustered index [EmailIndex] on [dbo].[AspNetUsers] ([NormalizedEmail] asc);
go

create unique nonclustered index [UserNameIndex] on [dbo].[AspNetUsers] ([NormalizedUserName] asc) where ([NormalizedUserName] is not null);
go


create table [dbo].[AspNetUserTokens] (
    [UserId] nvarchar(450) not null,
    [LoginProvider] nvarchar(128) not null,
    [Name] nvarchar(128) not null,
    [Value] nvarchar(MAX) null,
    constraint [PK_AspNetUserTokens] primary key clustered (
        [UserId] asc,
        [LoginProvider] asc,
        [Name] asc
        )
    );
go


alter table [dbo].[AspNetRoleClaims]
    with nocheck add constraint [FK_AspNetRoleClaims_AspNetRoles_RoleId] foreign key ([RoleId]) references [dbo].[AspNetRoles]([Id]) on delete cascade;
go

alter table [dbo].[AspNetUserClaims]
    with nocheck add constraint [FK_AspNetUserClaims_AspNetUsers_UserId] foreign key ([UserId]) references [dbo].[AspNetUsers]([Id]) on delete cascade;
go

alter table [dbo].[AspNetUserLogins]
    with nocheck add constraint [FK_AspNetUserLogins_AspNetUsers_UserId] foreign key ([UserId]) references [dbo].[AspNetUsers]([Id]) on delete cascade;
go

alter table [dbo].[AspNetUserRoles]
    with nocheck add constraint [FK_AspNetUserRoles_AspNetRoles_RoleId] foreign key ([RoleId]) references [dbo].[AspNetRoles]([Id]) on delete cascade;
go

alter table [dbo].[AspNetUserRoles]
    with nocheck add constraint [FK_AspNetUserRoles_AspNetUsers_UserId] foreign key ([UserId]) references [dbo].[AspNetUsers]([Id]) on delete cascade;
go

alter table [dbo].[AspNetUserTokens]
    with nocheck add constraint [FK_AspNetUserTokens_AspNetUsers_UserId] foreign key ([UserId]) references [dbo].[AspNetUsers]([Id]) on delete cascade;
go

alter table [dbo].[AspNetRoleClaims]
    with check check constraint [FK_AspNetRoleClaims_AspNetRoles_RoleId];

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].[AspNetUserTokens]
    with check check constraint [FK_AspNetUserTokens_AspNetUsers_UserId];
go

more info: https://aaron-hoffman.blogspot.com/2018/06/aspnet-core-21-identity.html