SQL 如何使用默认值修改列的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9299966/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to modify datatype of a column with a default value
提问by GregM
I'm trying to change the datatype of a column in SQL Server from tinyint to smallint.
我正在尝试将 SQL Server 中列的数据类型从 tinyint 更改为 smallint。
But there's a default value on my column and I don't know the name of the constraint.
但是我的列中有一个默认值，我不知道约束的名称。
Is there an easy way to do it ?
有没有简单的方法来做到这一点？
This don't work because of the default constraint :
由于默认约束，这不起作用：
ALTER TABLE mytable
Alter Column myColumn smallint NOT NULL default 1
回答by marc_s
You need to do this in several steps - first: drop the default constraint on your column, then modify your column.
您需要通过几个步骤来执行此操作 - 首先：删除列上的默认约束，然后修改您的列。
You could use code something like this:
你可以使用这样的代码：
-- find out the name of your default constraint -
-- assuming this is the only default constraint on your table
DECLARE @defaultconstraint sysname
SELECT @defaultconstraint = NAME
FROM sys.default_constraints
WHERE parent_object_id = object_ID('dbo.mytable')
-- declare a "DROP" statement to drop that default constraint
DECLARE @DropStmt NVARCHAR(500)
SET @DropStmt = 'ALTER TABLE dbo.mytable DROP CONSTRAINT ' + @defaultconstraint
-- drop the constraint
EXEC(@DropStmt)
-- alternatively: if you *know* the name of the default constraint - you can do this
-- more easily just by executing this single line of T-SQL code:
-- ALTER TABLE dbo.mytable DROP CONSTRAINT (fill in name of constraint here)
-- modify the column's datatype
ALTER TABLE dbo.mytable
Alter Column myColumn smallint NOT NULL
-- re-apply a default constraint - hint: give it a sensible name!
ALTER TABLE dbo.mytable
ADD CONSTRAINT DF_mytable_myColumn DEFAULT 1 FOR MyColumn
回答by Matt T
You could do it as a three step process
你可以把它作为一个三步过程
- add the new column with a different name,
- copy the values from the old column to the new
- drop the old column
- 添加具有不同名称的新列，
- 将旧列中的值复制到新列
- 删除旧列
It it matters that the name is the same, then repeat the process to change the name back.
重要的是名称是否相同，然后重复该过程以将名称改回。
回答by ron tornambe
You can find the constraint name for the default using MS Management Studio. Just find the tables folder for the given DB and look under Constraints. If there are many constraints, you can "Script the Constraint(s) to a query window which show the associated column name.
您可以使用 MS Management Studio 找到默认的约束名称。只需找到给定数据库的表文件夹并查看约束下。如果有很多约束，您可以“将约束脚本编写到显示相关列名称的查询窗口”。
