如何更改列以删除默认值?
该列是使用以下方式创建的:
ALTER table sometable Add somecolumn nchar(1) NOT NULL DEFAULT 'N'
然后被改变为:
alter table sometable alter column somecolumn nchar(1) null
这允许使用null,但默认值仍然存在。如何删除它?
如何更改列以删除默认值?
该列是使用以下方式创建的:
ALTER table sometable Add somecolumn nchar(1) NOT NULL DEFAULT 'N'
然后被改变为:
alter table sometable alter column somecolumn nchar(1) null
这允许使用null,但默认值仍然存在。如何删除它?
sp_help 'table_name'
获取约束的名称。 - Warios如果您不知道限制名称
DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__')
AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
WHERE NAME = N'__ColumnName__'
AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)
declare @name nvarchar(100)
select @name = [name] from sys.objects where type = 'D' and parent_object_id = object_id('sometable')
if (@name is not null)
begin
exec ('alter table [sometable] drop constraint [' + @name +']')
end
select @name = o.name from sys.objects o
join sys.columns col on o.object_id = col.default_object_id where type = 'D' and
parent_object_id = object_id('sometable') and col.name = 'someColumn'
declare @name nvarchar(100)
select * from sys.objects o
join sys.columns col on o.object_id = col.default_object_id
where
type = 'D' and parent_object_id = object_id('tablename')
and col.name = 'columnname'
- MirrorBoyselect name from sys.default_constraints where name like '%first_3_chars_of_field_name%'
DROP CONSTRAINT
来删除它。或者运行一个游标/while循环来删除数据库中所有类似的默认值。我也遇到了同样的问题,并且像你一样修改了表格并添加了默认值。但是以上解决方案都没有解决我的问题,因为尽管刷新了多次,默认值仍然未在约束列表中列出,但是在插入表格时却起到了作用。
最后,我通过在SQL Management Studio中右键单击表格名称并选择“设计”来解决了这个问题。然后,我在列属性中删除了那里的默认值。
alter table <tablename> drop constraint <constraintname>