How can I change data type of a column in SQL Server?

database-migration entity-framework entity-framework-6 sql-server

Question

Using SQL Server 2008 R2, I have.

I want to alter a column's data type (Size ) fromdecimal to int . First, I tried using the SQL Server designer, but SQL Server wouldn't let me. Please focus on the image.

So I made the decision to write a script. However, I encountered a problem.

  1. How would I go about doing it with a SQL script?

  2. How can I perform a database migration?

    For constructing databases, I used Entity Framework 6.x, C#, and the code-first methodology. I desire to keep my info inTag table.

  3. Is db-migration a superior tool? It seems uninteresting to me!

My example:

namespace Jahan.Blog.Model
{
   public class Tag : Entity, ITag
   {
    [Range(1, 4)]
    public virtual int Size { get; set; } //In db data type of Size is decimal.I want to change it to int.

    [Required]
    [StringLength(25)]
    public virtual string Title { get; set; }

    [StringLength(256)]
    public virtual string Description { get; set; }

    public virtual bool IsActive { get; set; }

    public virtual ISet<ArticleTag> ArticleTags { get; set; }       // FK

    public virtual ISet<ProjectTag> ProjectTags { get; set; }       // FK
   }
}

enter image description here

SQL code:

USE [Jahan-Blog]
GO

/****** Object:  Table [dbo].[Tag]    Script Date: 10/23/2014 16:17:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tag]') AND type in (N'U'))
DROP TABLE [dbo].[Tag]
GO

USE [Jahan-Blog]
GO

/****** Object:  Table [dbo].[Tag]    Script Date: 10/23/2014 16:17:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Tag](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](25) NOT NULL,
    [Description] [nvarchar](256) NULL,
    [IsActive] [bit] NOT NULL,
    [Size] [int] NOT NULL,
    [CreatedDate] [datetime] NULL,
    [ModifiedDate] [datetime] NULL,
 CONSTRAINT [PK_dbo.Tag] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

After running the script, I encountered this issue.

Msg 3726, Level 16, State 1, Line 4
Could not drop object 'dbo.Tag' because it is referenced by a FOREIGN KEY constraint.

Msg 2714, Level 16, State 6, Line 2
There is already an object named 'Tag' in the database.

Configuration for DbMigrations:

namespace Jahan.Blog.Web.Mvc.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;

//For knowing more information about db-migration please visit http://msdn.microsoft.com/en-gb/data/jj591621
internal sealed class Configuration 
    : DbMigrationsConfiguration<Jahan.Blog.Web.Mvc.Models.JahanBlogDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = false;
    }

    protected override void Seed(Jahan.Blog.Web.Mvc.Models.JahanBlogDbContext context)
    {
    }
   }
}
1
1
10/23/2014 1:41:46 PM

Accepted Answer

ZZZ_tmp
3
10/23/2014 1:49:02 PM

Popular Answer

You can edit SQL Server designer by clicking Options on the Tools menu, expanding Designers, and then selecting Table and Database Designers. Uncheck the Prevent saving modifications that force the creation of a new table check box.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow