Alter a Primary Key
in [Programming SQL]
|
From: Rick on 13 Apr 2010 16:41 I need to alter a primary key on a SQL 2000 table and make it a clustered index as well. When I try to do it through EM it gives me the error "Cannot convert a clustered index to a non clustered index using the drop existing." Even though I am trying to go to a clustered index. How would I do this through SQL. Here is my create script. Any help is greatly appreciated. CREATE UNIQUE CLUSTERED INDEX [PK_IndexName] ON [dbo].[TableName] ([ID1] desc , [ID2] desc ) WITH FILLFACTOR = 80 ,DROP_EXISTING ON [Indexes]
From: Tibor Karaszi on 13 Apr 2010 16:49 Try executing TSQL commands instead of using the GUI. For instance on my 2008, below executed with no errors (syntax is slightly different on 2000, but same principal should apply): CREATE TABLE a(c1 int CONSTRAINT PK_a PRIMARY KEY NONCLUSTERED) GO CREATE UNIQUE CLUSTERED INDEX PK_a ON a(c1) WITH DROP_EXISTING -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Rick" <Rick(a)discussions.microsoft.com> wrote in message news:8E51CB3A-55D7-42A3-9197-955C23556690(a)microsoft.com... > I need to alter a primary key on a SQL 2000 table and make it a clustered > index as well. When I try to do it through EM it gives me the error > "Cannot > convert a clustered index to a non clustered index using the drop > existing." > Even though I am trying to go to a clustered index. How would I do this > through SQL. Here is my create script. > > Any help is greatly appreciated. > > CREATE UNIQUE CLUSTERED > INDEX [PK_IndexName] ON [dbo].[TableName] ([ID1] desc , [ID2] desc ) > WITH > FILLFACTOR = 80 > ,DROP_EXISTING > ON [Indexes] >
From: Gert-Jan Strik on 13 Apr 2010 17:57 Rick wrote: > > I need to alter a primary key on a SQL 2000 table and make it a clustered > index as well. When I try to do it through EM it gives me the error "Cannot > convert a clustered index to a non clustered index using the drop existing." > Even though I am trying to go to a clustered index. How would I do this > through SQL. Here is my create script. > > Any help is greatly appreciated. > > CREATE UNIQUE CLUSTERED > INDEX [PK_IndexName] ON [dbo].[TableName] ([ID1] desc , [ID2] desc ) > WITH > FILLFACTOR = 80 > ,DROP_EXISTING > ON [Indexes] Rick, Your statement should work just fine, provided that the current constraint/index name is in fact PK_IndexName. If not, then you can use sp_help "dbo.TableName" to find out the name of the existing index. -- Gert-Jan
|
Pages: 1 Prev: Create Adjancency Model From Flat Data? Next: T-SQL cursor - Variables in an email body |