更改SQL Server中列的数据类型 [英] Changing data type of column in SQL Server

问题描述

我必须更改SQL Server中列的数据类型。

我知道我必须删除索引和其他约束吗？

我必须删除非空检查吗？

在更改数据类型之前，我还需要检查什么？

我需要删除约束并更改表，然后再次添加约束。

这是正确的方法吗？

  DROP INDEX UX_1_COMPUTATION on dbo.Computation 
 
 ALTER TABLE dbo.Computation 
 ALTER COLUMN ComputationID NVARCHAR（25）
 
 CREATE UNIQUE INDEX UX_1_COMPUTATION ON dbo.Computation（ComputationID）; 
  

这里 UX_1_COMPUTATION 是唯一的索引名称， 计算是表名，
ComputationID 是列名。

这是否正确？

更新

所以如果有一个复合索引，其中涉及到多个列，我该如何处理？这些索引包含具有非主键列的主键列。

当我尝试执行以下语句时

  DROP INDEX UX_2_COMPUTATION on dbo.Computation 
 
 ALTER TABLE dbo.Computation 
 ALTER COLUMN ComputationID NVARCHAR（25）
 
 CREATE UNIQUE INDEX UX_2_COMPUTATION on dbo.Computation（ComputationID，ComputeGuid）; 
  

它抛出以下异常

SQL DROP INDEX UX_2_COMPUTATION

  ON dbo.Computation 
 
 ALTER TABLE dbo.Computation 
 
 ALTER COLUMN ComputationID NVARCHAR（10）不为空
 
 CREATE INDEX UX_2_COMPUTATION 
 
在dbo.Computation （ComputationID，ComputeGuid）：对象
 
'PK_Computation'依赖于列'ComputationID'：
 
导致的执行失败SQL DROP INDEX UX_2_COMPUTATION 
 
 ON dbo.Computation 
 
 ALTER TABLE dbo.Computation 
 
 ALTER COLUMN ComputationID NVARCHAR（10）不为空
 
 CREATE INDEX UX_2_COMPUTATION 
 
 ON dbo.Computation（ComputationID，ComputeGuid）：对象
 
'PK_Computation'依赖于列'ComputationID'：
 
原因：对象PK_Computation依赖于列ComputationID。非常感谢，
 
 
 
 -Sam  
 p> 
 
解决方案
您的当前语句将列更改为可空 NVARCHAR（25） column  - 是你想要的吗？
 
 
 
如果你想确保你的列是 NON NULL ，您必须显式声明：
  ALTER TABLE dbo.Computation 
 ALTER COLUMN ComputationID NVARCHAR NOT NULL 
  
 
I have to change datatype of  a column in SQL Server. So what are the constraints in doing?

I know I have to remove index and other constraints?

Do I have to remove not null check ?

What other things do I have to check before altering the datatype?

I need to remove the constraints and alter the table and then add the constraints again.

Is this the right way to do so ? 
DROP INDEX UX_1_COMPUTATION ON  dbo.Computation 

ALTER TABLE dbo.Computation
ALTER COLUMN ComputationID NVARCHAR(25) 

CREATE UNIQUE INDEX UX_1_COMPUTATION ON dbo.Computation (ComputationID);
Here UX_1_COMPUTATION is the unique index name, Computation is the table name and 
ComputationID is the column name.

Is this correct ?

Update

So if there is a composite index where there are more than one column involved , How do i deal with it ? These indexes contains primary key column with non primary key columns .

When i tried executing the following statement
DROP INDEX UX_2_COMPUTATION ON  dbo.Computation 

ALTER TABLE dbo.Computation
ALTER COLUMN ComputationID NVARCHAR(25) 

CREATE UNIQUE INDEX UX_2_COMPUTATION ON dbo.Computation (ComputationID , ComputeGuid);
It is throwing the following exception

  
SQL DROP INDEX UX_2_COMPUTATION 


        ON dbo.Computation

        ALTER TABLE dbo.Computation

        ALTER COLUMN ComputationID  NVARCHAR(10) Not Null

        CREATE INDEX UX_2_COMPUTATION 

        ON dbo.Computation (ComputationID , ComputeGuid): The object 

'PK_Computation' is dependent on column 'ComputationID '.:

      Caused By: Error executing SQL DROP INDEX UX_2_COMPUTATION 

        ON dbo.Computation

        ALTER TABLE dbo.Computation

        ALTER COLUMN ComputationID  NVARCHAR(10) Not Null

        CREATE INDEX UX_2_COMPUTATION 

        ON dbo.Computation (ComputationID , ComputeGuid): The object 

'PK_Computation' is dependent on column 'ComputationID '.:

      Caused By: The object 'PK_Computation' is dependent on column 'ComputationID '.
Thanks,

-Sam
解决方案
Your current statement will change the column to be a nullable NVARCHAR(25) column - is that what you want?

If you want to be sure that your column is going to be NON NULL, you have to explicitly state this:
ALTER TABLE dbo.Computation
ALTER COLUMN ComputationID NVARCHAR(25) NOT NULL


                        
这篇关于更改SQL Server中列的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！