如何修改具有默认值的列的数据类型

发布于 2025-01-06 03:17:05 字数 220 浏览 0 评论 0原文

我正在尝试将 SQL Server 中列的数据类型从tinyint 更改为smallint。

但我的列上有一个默认值，但我不知道约束的名称。

有简单的方法吗？

由于默认约束，这不起作用：

ALTER TABLE mytable
Alter Column myColumn smallint NOT NULL default 1

原文

I'm trying to change the datatype of a column in SQL Server from tinyint to 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夜夜流光相皎洁 2025-01-13 03:17:05

您需要分几个步骤来完成此操作 - 首先：删除列上的默认约束，然后修改列。

你可以使用类似这样的代码：

-- 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

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

回复收藏 0 原文