Base solution for your next web application
Open Closed

Changing columns datatype #1539

User avatar
0
nikko created

I want to change column datatype. For example I don't need all the columns to be nvarchar and also I think 256 is too much for an e-mail column. What would be the best way of achieving this?

I tried to override OnModelCreating with:

base.OnModelCreating(modelBuilder); modelBuilder.ChangeAbpTablePrefix<Tenant, Role, User>(""); modelBuilder.Properties<string>().Configure(p => p.IsUnicode(false));

But I get this error when trying to update the database:

The index 'IX_Discriminator_TenantId_Name' is dependent on column 'Name'. The index 'IX_Discriminator_EditionId_Name' is dependent on column 'Name'. The index 'IX_TenantId_Name' is dependent on column 'Name'. ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column.

Any ideas?

Go to accepted answer
4 Answer(s)
  • User Avatar
    0
    hikalkan created
    Support Team

    Have you created migration (using add-migration command)? If so, can you share what it generated. Maybe it's dropping column or maybe SQL server does not allow such an operation.

  • User Avatar
    0
    nikko created

    Sure:

    public override void Up()
        {
            DropIndex("dbo.NotificationSubscriptions", new[] { "NotificationName", "EntityTypeName", "EntityId", "UserId" });
            DropIndex("dbo.UserLoginAttempts", new[] { "TenancyName", "UserNameOrEmailAddress", "Result" });
            AlterColumn("dbo.AuditLogs", "ServiceName", c => c.String(maxLength: 256, unicode: false));
            AlterColumn("dbo.AuditLogs", "MethodName", c => c.String(maxLength: 256, unicode: false));
            AlterColumn("dbo.AuditLogs", "Parameters", c => c.String(maxLength: 1024, unicode: false));
            AlterColumn("dbo.AuditLogs", "ClientIpAddress", c => c.String(maxLength: 64, unicode: false));
            AlterColumn("dbo.AuditLogs", "ClientName", c => c.String(maxLength: 128, unicode: false));
            AlterColumn("dbo.AuditLogs", "BrowserInfo", c => c.String(maxLength: 256, unicode: false));
            AlterColumn("dbo.AuditLogs", "Exception", c => c.String(maxLength: 2000, unicode: false));
            AlterColumn("dbo.AuditLogs", "CustomData", c => c.String(maxLength: 2000, unicode: false));
            AlterColumn("dbo.BackgroundJobs", "JobType", c => c.String(nullable: false, maxLength: 512, unicode: false));
            AlterColumn("dbo.BackgroundJobs", "JobArgs", c => c.String(nullable: false, unicode: false));
            AlterColumn("dbo.Features", "Name", c => c.String(nullable: false, maxLength: 128, unicode: false));
            AlterColumn("dbo.Features", "Value", c => c.String(nullable: false, maxLength: 2000, unicode: false));
            AlterColumn("dbo.Editions", "Name", c => c.String(nullable: false, maxLength: 32, unicode: false));
            AlterColumn("dbo.Editions", "DisplayName", c => c.String(nullable: false, maxLength: 64, unicode: false));
            AlterColumn("dbo.Languages", "Name", c => c.String(nullable: false, maxLength: 10, unicode: false));
            AlterColumn("dbo.Languages", "DisplayName", c => c.String(nullable: false, maxLength: 64, unicode: false));
            AlterColumn("dbo.Languages", "Icon", c => c.String(maxLength: 128, unicode: false));
            AlterColumn("dbo.LanguageTexts", "LanguageName", c => c.String(nullable: false, maxLength: 10, unicode: false));
            AlterColumn("dbo.LanguageTexts", "Source", c => c.String(nullable: false, maxLength: 128, unicode: false));
            AlterColumn("dbo.LanguageTexts", "Key", c => c.String(nullable: false, maxLength: 256, unicode: false));
            AlterColumn("dbo.LanguageTexts", "Value", c => c.String(nullable: false, unicode: false));
            AlterColumn("dbo.Notifications", "NotificationName", c => c.String(nullable: false, maxLength: 96, unicode: false));
            AlterColumn("dbo.Notifications", "Data", c => c.String(unicode: false));
            AlterColumn("dbo.Notifications", "DataTypeName", c => c.String(maxLength: 512, unicode: false));
            AlterColumn("dbo.Notifications", "EntityTypeName", c => c.String(maxLength: 250, unicode: false));
            AlterColumn("dbo.Notifications", "EntityTypeAssemblyQualifiedName", c => c.String(maxLength: 512, unicode: false));
            AlterColumn("dbo.Notifications", "EntityId", c => c.String(maxLength: 96, unicode: false));
            AlterColumn("dbo.Notifications", "UserIds", c => c.String(unicode: false));
            AlterColumn("dbo.Notifications", "ExcludedUserIds", c => c.String(unicode: false));
            AlterColumn("dbo.Notifications", "TenantIds", c => c.String(unicode: false));
            AlterColumn("dbo.NotificationSubscriptions", "NotificationName", c => c.String(maxLength: 96, unicode: false));
            AlterColumn("dbo.NotificationSubscriptions", "EntityTypeName", c => c.String(maxLength: 250, unicode: false));
            AlterColumn("dbo.NotificationSubscriptions", "EntityTypeAssemblyQualifiedName", c => c.String(maxLength: 512, unicode: false));
            AlterColumn("dbo.NotificationSubscriptions", "EntityId", c => c.String(maxLength: 96, unicode: false));
            AlterColumn("dbo.OrganizationUnits", "Code", c => c.String(nullable: false, maxLength: 128, unicode: false));
            AlterColumn("dbo.OrganizationUnits", "DisplayName", c => c.String(nullable: false, maxLength: 128, unicode: false));
            AlterColumn("dbo.Permissions", "Name", c => c.String(nullable: false, maxLength: 128, unicode: false));
            AlterColumn("dbo.Roles", "DisplayName", c => c.String(nullable: false, maxLength: 64, unicode: false));
            AlterColumn("dbo.Roles", "Name", c => c.String(nullable: false, maxLength: 32, unicode: false));
            AlterColumn("dbo.Users", "AuthenticationSource", c => c.String(maxLength: 64, unicode: false));
            AlterColumn("dbo.Users", "Name", c => c.String(nullable: false, maxLength: 32, unicode: false));
            AlterColumn("dbo.Users", "Surname", c => c.String(nullable: false, maxLength: 32, unicode: false));
            AlterColumn("dbo.Users", "Password", c => c.String(nullable: false, maxLength: 128, unicode: false));
            AlterColumn("dbo.Users", "EmailConfirmationCode", c => c.String(maxLength: 128, unicode: false));
            AlterColumn("dbo.Users", "PasswordResetCode", c => c.String(maxLength: 328, unicode: false));
            AlterColumn("dbo.Users", "UserName", c => c.String(nullable: false, maxLength: 32, unicode: false));
            AlterColumn("dbo.Users", "EmailAddress", c => c.String(nullable: false, maxLength: 256, unicode: false));
            AlterColumn("dbo.UserLogins", "LoginProvider", c => c.String(nullable: false, maxLength: 128, unicode: false));
            AlterColumn("dbo.UserLogins", "ProviderKey", c => c.String(nullable: false, maxLength: 256, unicode: false));
            AlterColumn("dbo.Settings", "Name", c => c.String(nullable: false, maxLength: 256, unicode: false));
            AlterColumn("dbo.Settings", "Value", c => c.String(maxLength: 2000, unicode: false));
            AlterColumn("dbo.TenantNotifications", "NotificationName", c => c.String(nullable: false, maxLength: 96, unicode: false));
            AlterColumn("dbo.TenantNotifications", "Data", c => c.String(unicode: false));
            AlterColumn("dbo.TenantNotifications", "DataTypeName", c => c.String(maxLength: 512, unicode: false));
            AlterColumn("dbo.TenantNotifications", "EntityTypeName", c => c.String(maxLength: 250, unicode: false));
            AlterColumn("dbo.TenantNotifications", "EntityTypeAssemblyQualifiedName", c => c.String(maxLength: 512, unicode: false));
            AlterColumn("dbo.TenantNotifications", "EntityId", c => c.String(maxLength: 96, unicode: false));
            AlterColumn("dbo.Tenants", "Name", c => c.String(nullable: false, maxLength: 128, unicode: false));
            AlterColumn("dbo.Tenants", "TenancyName", c => c.String(nullable: false, maxLength: 64, unicode: false));
            AlterColumn("dbo.Tenants", "ConnectionString", c => c.String(maxLength: 1024, unicode: false));
            AlterColumn("dbo.UserAccounts", "UserName", c => c.String(unicode: false));
            AlterColumn("dbo.UserAccounts", "EmailAddress", c => c.String(unicode: false));
            AlterColumn("dbo.UserLoginAttempts", "TenancyName", c => c.String(maxLength: 64, unicode: false));
            AlterColumn("dbo.UserLoginAttempts", "UserNameOrEmailAddress", c => c.String(maxLength: 255, unicode: false));
            AlterColumn("dbo.UserLoginAttempts", "ClientIpAddress", c => c.String(maxLength: 64, unicode: false));
            AlterColumn("dbo.UserLoginAttempts", "ClientName", c => c.String(maxLength: 128, unicode: false));
            AlterColumn("dbo.UserLoginAttempts", "BrowserInfo", c => c.String(maxLength: 256, unicode: false));
            CreateIndex("dbo.NotificationSubscriptions", new[] { "NotificationName", "EntityTypeName", "EntityId", "UserId" });
            CreateIndex("dbo.UserLoginAttempts", new[] { "TenancyName", "UserNameOrEmailAddress", "Result" });
        }
        
        public override void Down()
        {
            DropIndex("dbo.UserLoginAttempts", new[] { "TenancyName", "UserNameOrEmailAddress", "Result" });
            DropIndex("dbo.NotificationSubscriptions", new[] { "NotificationName", "EntityTypeName", "EntityId", "UserId" });
            AlterColumn("dbo.UserLoginAttempts", "BrowserInfo", c => c.String(maxLength: 256));
            AlterColumn("dbo.UserLoginAttempts", "ClientName", c => c.String(maxLength: 128));
            AlterColumn("dbo.UserLoginAttempts", "ClientIpAddress", c => c.String(maxLength: 64));
            AlterColumn("dbo.UserLoginAttempts", "UserNameOrEmailAddress", c => c.String(maxLength: 255));
            AlterColumn("dbo.UserLoginAttempts", "TenancyName", c => c.String(maxLength: 64));
            AlterColumn("dbo.UserAccounts", "EmailAddress", c => c.String());
            AlterColumn("dbo.UserAccounts", "UserName", c => c.String());
            AlterColumn("dbo.Tenants", "ConnectionString", c => c.String(maxLength: 1024));
            AlterColumn("dbo.Tenants", "TenancyName", c => c.String(nullable: false, maxLength: 64));
            AlterColumn("dbo.Tenants", "Name", c => c.String(nullable: false, maxLength: 128));
            AlterColumn("dbo.TenantNotifications", "EntityId", c => c.String(maxLength: 96));
            AlterColumn("dbo.TenantNotifications", "EntityTypeAssemblyQualifiedName", c => c.String(maxLength: 512));
            AlterColumn("dbo.TenantNotifications", "EntityTypeName", c => c.String(maxLength: 250));
            AlterColumn("dbo.TenantNotifications", "DataTypeName", c => c.String(maxLength: 512));
            AlterColumn("dbo.TenantNotifications", "Data", c => c.String());
            AlterColumn("dbo.TenantNotifications", "NotificationName", c => c.String(nullable: false, maxLength: 96));
            AlterColumn("dbo.Settings", "Value", c => c.String(maxLength: 2000));
            AlterColumn("dbo.Settings", "Name", c => c.String(nullable: false, maxLength: 256));
            AlterColumn("dbo.UserLogins", "ProviderKey", c => c.String(nullable: false, maxLength: 256));
            AlterColumn("dbo.UserLogins", "LoginProvider", c => c.String(nullable: false, maxLength: 128));
            AlterColumn("dbo.Users", "EmailAddress", c => c.String(nullable: false, maxLength: 256));
            AlterColumn("dbo.Users", "UserName", c => c.String(nullable: false, maxLength: 32));
            AlterColumn("dbo.Users", "PasswordResetCode", c => c.String(maxLength: 328));
            AlterColumn("dbo.Users", "EmailConfirmationCode", c => c.String(maxLength: 128));
            AlterColumn("dbo.Users", "Password", c => c.String(nullable: false, maxLength: 128));
            AlterColumn("dbo.Users", "Surname", c => c.String(nullable: false, maxLength: 32));
            AlterColumn("dbo.Users", "Name", c => c.String(nullable: false, maxLength: 32));
            AlterColumn("dbo.Users", "AuthenticationSource", c => c.String(maxLength: 64));
            AlterColumn("dbo.Roles", "Name", c => c.String(nullable: false, maxLength: 32));
            AlterColumn("dbo.Roles", "DisplayName", c => c.String(nullable: false, maxLength: 64));
            AlterColumn("dbo.Permissions", "Name", c => c.String(nullable: false, maxLength: 128));
            AlterColumn("dbo.OrganizationUnits", "DisplayName", c => c.String(nullable: false, maxLength: 128));
            AlterColumn("dbo.OrganizationUnits", "Code", c => c.String(nullable: false, maxLength: 128));
            AlterColumn("dbo.NotificationSubscriptions", "EntityId", c => c.String(maxLength: 96));
            AlterColumn("dbo.NotificationSubscriptions", "EntityTypeAssemblyQualifiedName", c => c.String(maxLength: 512));
            AlterColumn("dbo.NotificationSubscriptions", "EntityTypeName", c => c.String(maxLength: 250));
            AlterColumn("dbo.NotificationSubscriptions", "NotificationName", c => c.String(maxLength: 96));
            AlterColumn("dbo.Notifications", "TenantIds", c => c.String());
            AlterColumn("dbo.Notifications", "ExcludedUserIds", c => c.String());
            AlterColumn("dbo.Notifications", "UserIds", c => c.String());
            AlterColumn("dbo.Notifications", "EntityId", c => c.String(maxLength: 96));
            AlterColumn("dbo.Notifications", "EntityTypeAssemblyQualifiedName", c => c.String(maxLength: 512));
            AlterColumn("dbo.Notifications", "EntityTypeName", c => c.String(maxLength: 250));
            AlterColumn("dbo.Notifications", "DataTypeName", c => c.String(maxLength: 512));
            AlterColumn("dbo.Notifications", "Data", c => c.String());
            AlterColumn("dbo.Notifications", "NotificationName", c => c.String(nullable: false, maxLength: 96));
            AlterColumn("dbo.LanguageTexts", "Value", c => c.String(nullable: false));
            AlterColumn("dbo.LanguageTexts", "Key", c => c.String(nullable: false, maxLength: 256));
            AlterColumn("dbo.LanguageTexts", "Source", c => c.String(nullable: false, maxLength: 128));
            AlterColumn("dbo.LanguageTexts", "LanguageName", c => c.String(nullable: false, maxLength: 10));
            AlterColumn("dbo.Languages", "Icon", c => c.String(maxLength: 128));
            AlterColumn("dbo.Languages", "DisplayName", c => c.String(nullable: false, maxLength: 64));
            AlterColumn("dbo.Languages", "Name", c => c.String(nullable: false, maxLength: 10));
            AlterColumn("dbo.Editions", "DisplayName", c => c.String(nullable: false, maxLength: 64));
            AlterColumn("dbo.Editions", "Name", c => c.String(nullable: false, maxLength: 32));
            AlterColumn("dbo.Features", "Value", c => c.String(nullable: false, maxLength: 2000));
            AlterColumn("dbo.Features", "Name", c => c.String(nullable: false, maxLength: 128));
            AlterColumn("dbo.BackgroundJobs", "JobArgs", c => c.String(nullable: false));
            AlterColumn("dbo.BackgroundJobs", "JobType", c => c.String(nullable: false, maxLength: 512));
            AlterColumn("dbo.AuditLogs", "CustomData", c => c.String(maxLength: 2000));
            AlterColumn("dbo.AuditLogs", "Exception", c => c.String(maxLength: 2000));
            AlterColumn("dbo.AuditLogs", "BrowserInfo", c => c.String(maxLength: 256));
            AlterColumn("dbo.AuditLogs", "ClientName", c => c.String(maxLength: 128));
            AlterColumn("dbo.AuditLogs", "ClientIpAddress", c => c.String(maxLength: 64));
            AlterColumn("dbo.AuditLogs", "Parameters", c => c.String(maxLength: 1024));
            AlterColumn("dbo.AuditLogs", "MethodName", c => c.String(maxLength: 256));
            AlterColumn("dbo.AuditLogs", "ServiceName", c => c.String(maxLength: 256));
            CreateIndex("dbo.UserLoginAttempts", new[] { "TenancyName", "UserNameOrEmailAddress", "Result" });
            CreateIndex("dbo.NotificationSubscriptions", new[] { "NotificationName", "EntityTypeName", "EntityId", "UserId" });
        }

    Anyway, if we cannot change like this, is there another way? Unicode columns take twice the space if compared to non-unicode.

  • User Avatar
    0
    nikko created

    Does anyone know a better solution?

  • User Avatar
    0
    vineforce created

    1.remove the indexing first for which it raised the error 2 apply changes of your column 3.create indexes which was deleted in 1 step.

Assignee
No one
Labels
Non yet