sql 主键 标识 默认值

 

 

SET IDENTITY_INSERT [dbo].[t_message] ON

INSERT INTO [dbo].[t_message]   (f_mid )

VALUES (55)

SET IDENTITY_INSERT [dbo].[t_message] OFF
GO

select * from [dbo].[t_message] order by f_mid desc

 

标识序列
dbcc checkident('t_member',reseed,@@IDENTITY)

 主键

select name from sysobjects 
where parent_obj=object_id('abc') and  xtype='pk'

alter table abc add constraint PK_bbb primary key(bbb)
alter table abc drop constraint PK_bbb

 

 标识修改

四个步骤：
1、增加一个int类型的列
   alter table add xinglie int
 2、更新新列的数据为自增列的数据
   update tb set xinglie=自增列名
3、删除自增列
   alter table tb drop column 自增列
4、更改新列名为原自增列的列名
   exec sp_rename N'tb.xinglie','原自增列名',N'column'  

 默认值

--说明：删除表的字段的原有约束  
alter table abc drop constraint DF_bbb      
--说明：添加一个表的字段的约束并指定默认值
alter table abc add constraint DF_ccc DEFAULT 5 for ccc
--查表字段的默认值
select b.text as df  
from syscolumns a left join syscomments b on a.cdefault=b.id   
where a.id=object_id('abc') and a.name='ccc' 
--查表字段的默认值的约束
select b.name,a.* from sysobjects a,syscolumns b
where a.id=b.cdefault and a.parent_obj=object_id('abc') and  a.xtype='D' and b.name='ccc'