我有一个表,有聚集唯一索引,但没有主键
现在我想在这个聚集索引上加主键,1,我不能删除过去的聚集索引
2,现在正常加主键,会生成一个非聚集索引,这样也不行./*
CREATE TABLE [dbo].[table3](
[log1] [int] IDENTITY(1,1) NOT NULL,
[data1] [varchar](50) NULL
) ON [PRIMARY]CREATE UNIQUE CLUSTERED INDEX [idx_table3] ON [dbo].[table3]
(
[log1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]*/这个是初始化脚本,不要告诉我改脚本,这个是为了大家实验方便,
现在是已经有这样的表,怎么去加主键.
现在我想在这个聚集索引上加主键,1,我不能删除过去的聚集索引
2,现在正常加主键,会生成一个非聚集索引,这样也不行./*
CREATE TABLE [dbo].[table3](
[log1] [int] IDENTITY(1,1) NOT NULL,
[data1] [varchar](50) NULL
) ON [PRIMARY]CREATE UNIQUE CLUSTERED INDEX [idx_table3] ON [dbo].[table3]
(
[log1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]*/这个是初始化脚本,不要告诉我改脚本,这个是为了大家实验方便,
现在是已经有这样的表,怎么去加主键.
GO
ALTER TABLE dbo.table3 ADD CONSTRAINT
idx_table3 PRIMARY KEY CLUSTERED
(
[log1] ASC
)
exec sp_helpindex [dbo].[table3]
--然后删除它
drop index 你在主键上的非聚集索引名
--第一步
alter table [dbo].[table3] add constraint PK_lg primary key (log1)
--首先找出那个非聚集索引的名字
exec sp_helpindex tb
--然后删除它
drop index 你在主键上的非聚集索引名
另外 楼主不好意思哈 我不应该往下定论的
alter table [dbo].[table3] add constraint PK_lg primary key (log1)--首先找出那个主键上的非聚集索引的名字
exec sp_helpindex [dbo].[table3]
--然后删除它
drop index 你在主键上的非聚集索引名
[log1] [int] IDENTITY(1,1) NOT NULL,
[data1] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [idx_table3] ON [dbo].[table3]
(
[log1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--插入数据
insert into table3 select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
union all select 'e'
union all select 'f'
select * from table3
--创建过度表
create table ta(log1 int identity primary key,data1 varchar(50))
set IDENTITY_INSERT ta on
insert into ta(log1,data1) select log1,data1 from table3
set IDENTITY_INSERT ta off--删除原表
drop table table3
--重命名过度表
exec sp_rename 'ta','table3'
遇到这样的问题怎么解决啊