create table ba (oidID int primary key, telphone varchar(8), email varchar(15) )sp_help baalter table ba drop constraint PK__ba__76969D2Ealter table ba add ID int primary keysp_help ba
其中PK__ba__76969D2E使用sp_help ba 看出来的约束值,你的表中不一定是PK__ba__76969D2E ,必须要看下才行若ID字段已添加则必要使用alter table ba add constraint PK_T4_num primary key (id)其中PK_T4_num是主键约束名,这个可以自己加
--创建表 if object_id('andy') is not null drop table dbo.andy create table dbo.andy (oidID int primary key, telphone varchar(8), email varchar(15) ) --查看表结构 exec sp_help andy --删除主键 declare @var varchar(50) select top 1 @var = name from sys.objects with (nolock) where type ='pk' and object_name(parent_object_id)='andy' exec ('alter table dbo.andy drop constraint '+ @var)--增加主键和自增列 alter table dbo.andy add ID int identity(1,1) primary key --查看新的表结构 exec sp_help andy
刚才是sql server 2005下运行下面是sql server 2000下运行: --创建表 if object_id('andy') is not null drop table dbo.andy create table dbo.andy (oidID int primary key, telphone varchar(8), email varchar(15) ) --查看表结构 exec sp_help andy --删除主键 declare @var varchar(50) select top 1 @var = name from sysobjects with (nolock) where xtype ='pk' and object_name(parent_obj)='andy' exec ('alter table dbo.andy drop constraint '+ @var)--增加主键和自增列 alter table dbo.andy add ID int identity(1,1) primary key exec sp_help andy
执行到alter table ba add ID int primary key ;这时候就出错消息 4901,级别 16,状态 1,第 1 行 ALTER TABLE 只允许添加满足下述条件的列: 列可以包含空值;或者列具有指定的 DEFAULT 定义;或者要添加的列是标识列或时间戳列;或者,如果前几个条件均未满足,则表必须为空以允许添加此列。不能将列"ID"添加到非空表"ba"中,因为它不满足上述条件。 应该怎么做啊
谢谢大家了 yyyyzzzz_2002的能完全执行 谢谢了
忘记写no null 改成 alter table ba add ID int not null primary key不过奇怪的是刚刚在2005中不加not null也成功了
(oidID int primary key,
telphone varchar(8),
email varchar(15)
)sp_help baalter table ba drop constraint PK__ba__76969D2Ealter table ba add ID int primary keysp_help ba
if object_id('andy') is not null
drop table dbo.andy
create table dbo.andy
(oidID int primary key,
telphone varchar(8),
email varchar(15)
)
--查看表结构
exec sp_help andy --删除主键
declare @var varchar(50)
select top 1 @var = name from sys.objects with (nolock)
where type ='pk' and object_name(parent_object_id)='andy'
exec ('alter table dbo.andy drop constraint '+ @var)--增加主键和自增列
alter table dbo.andy add ID int identity(1,1) primary key
--查看新的表结构
exec sp_help andy
--创建表
if object_id('andy') is not null
drop table dbo.andy
create table dbo.andy
(oidID int primary key,
telphone varchar(8),
email varchar(15)
)
--查看表结构
exec sp_help andy --删除主键
declare @var varchar(50)
select top 1 @var = name from sysobjects with (nolock)
where xtype ='pk' and object_name(parent_obj)='andy'
exec ('alter table dbo.andy drop constraint '+ @var)--增加主键和自增列
alter table dbo.andy add ID int identity(1,1) primary key exec sp_help andy
执行到alter table ba add ID int primary key ;这时候就出错消息 4901,级别 16,状态 1,第 1 行
ALTER TABLE 只允许添加满足下述条件的列: 列可以包含空值;或者列具有指定的 DEFAULT 定义;或者要添加的列是标识列或时间戳列;或者,如果前几个条件均未满足,则表必须为空以允许添加此列。不能将列"ID"添加到非空表"ba"中,因为它不满足上述条件。
应该怎么做啊
忘记写no null
改成
alter table ba add ID int not null primary key不过奇怪的是刚刚在2005中不加not null也成功了