試一下下面的行不行~ alter table tb add 年龄 int not null go update dbo.syscolumns set colid=case when colid=5 then 3 else colid+1 end where id=OBJECT_ID(N'tb') and colid>2
改一下,試一下下面的行不行~ alter table tb add 年龄 int not null go update dbo.syscolumns set colid=case when colid=5 then 3 else colid+1 end , colorder=case when colorder=5 then 3 else colorder+1 end , where id=OBJECT_ID(N'tb') and colid>2
再加上下面的 exec sp_configure 'allow updates','0' go reconfigure with override
alter table tb add 年龄 int NOT NULL 为什么提示需要有默认值
alter table tabel1 add [年龄] int not null
我也是这样写的,但是报错: ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'age1' cannot be added to non-empty table 'student' because it does not satisfy these conditions.谁知道这个怎么回事
如果添加列到表中為not null,就必須指定默認值 下面是修改過的 alter table tb add 年龄 int not null default 0 go update dbo.syscolumns set colid=case when colid=5 then 3 else colid+1 end where id=OBJECT_ID(N'tb') and colid>2 go exec sp_configure 'allow updates',1 go reconfigure with override
alter table tb
add 年龄 int not null
go
update dbo.syscolumns
set colid=case when colid=5 then 3 else colid+1 end
where id=OBJECT_ID(N'tb') and colid>2
alter table tb
add 年龄 int not null
go
update dbo.syscolumns
set colid=case when colid=5 then 3 else colid+1 end ,
colorder=case when colorder=5 then 3 else colorder+1 end ,
where id=OBJECT_ID(N'tb') and colid>2
exec sp_configure 'allow updates','0'
go
reconfigure with override
为什么提示需要有默认值
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'age1' cannot be added to non-empty table 'student' because it does not satisfy these conditions.谁知道这个怎么回事
下面是修改過的
alter table tb
add 年龄 int not null default 0
go
update dbo.syscolumns
set colid=case when colid=5 then 3 else colid+1 end
where id=OBJECT_ID(N'tb') and colid>2
go
exec sp_configure 'allow updates',1
go
reconfigure with override