修改主键约约束的语句,在SQL2005上可以通过.
客户现场有SQl2000,结果说过不去,具体是什么错,手上没有SQL2000,无法验证。表tblA有六个字段,其中设置四个作为联合主键
ALTER TABLE [dbo].[tblA] ADD CONSTRAINT [PK_tblA] PRIMARY KEY NONCLUSTERED
( [Field1] ASC, [Field2] ASC, [Field3] ASC, [Field4] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
客户现场有SQl2000,结果说过不去,具体是什么错,手上没有SQL2000,无法验证。表tblA有六个字段,其中设置四个作为联合主键
ALTER TABLE [dbo].[tblA] ADD CONSTRAINT [PK_tblA] PRIMARY KEY NONCLUSTERED
( [Field1] ASC, [Field2] ASC, [Field3] ASC, [Field4] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
所以要干掉
( [Field1] ASC, [Field2] ASC, [Field3] ASC, [Field4] ASC )
查到一个例子.This example uses the FILLFACTOR clause set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that index values in the table will never change.
SET NOCOUNT OFF USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name = 'zip_ind') DROP INDEX authors.zip_ind GO USE pubs GO CREATE NONCLUSTERED INDEX zip_ind ON authors (zip) WITH FILLFACTOR = 100
设置主键alter table <表名> add constraint <主键名> primary key(<列名>)
设置联合主键alter table <表名> add constraint <主键名> primary key(<列名1>,<列名2>,...,<列名N>)
约束alter table <表名> add constraint <约束名> check (<约束语句>)在输入语句的时候<>是不需要的
Create 是OK的
SET NOCOUNT OFF USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name = 'zip_ind') DROP INDEX authors.zip_ind GO USE pubs GOCREATE NONCLUSTERED INDEX zip_ind ON authors (zip)WITH FILLFACTOR = 100
ALTER TABLE [dbo].[tblA] ADD CONSTRAINT [PK_tblA] PRIMARY KEY NONCLUSTERED
( [Field1] ASC, [Field2] ASC, [Field3] ASC, [Field4] ASC )
之后,再如何加上填充因子?
后来因为要修改其中[Field2]列所有的数据, 这样就有可能造成有些记录的主键是重复的.所以现在第一步是取消了主键.
第二步修改数据.
第三步查询是不是有重复主键的数据,如果有的话,删除.
第四步再加上主键.