How do I ALTER a table''s PK defintion.

• Express12

  SSCoach

  Points: 18514

  More actions

  August 9, 2006 at 9:15 am

  #61706

  How do I ALTER a table's PK defintion?   TAB_a is defined w/ a "NONclustered PK" IDX on COL_1.

  I need to alter TAB_a w/ a "CLUSTERED PK" on COL_1, COL_2, COL_3.

  FYI:

  >> I tried DROPPING THE Exisitng PK and recv'd error:

  Msg 3723, Level 16, State 4, Line 1

  An explicit DROP INDEX is not allowed on index

  'TAB_a.PK_TAB_a'. It is being used for PRIMARY KEY constraint enforcement.

  >> I tried ALTERing the Existing PK:

  ALTER TABLE [dbo].[TAB_a] ADD CONSTRAINT [PK_c_Index_1] PRIMARY KEY CLUSTERED

   ([COL_1] ASC, [COL_2] ASC, [COL_3] ASC) WITH FILLFACTOR = 80 ON [PRIMARY]

   and recv'd error:

  Msg 1779, Level 16, State 0, Line 1

  Table 'TAB_a' already has a primary key defined on it.

  Msg 1750, Level 16, State 0, Line 1

  Could not create constraint. See previous errors.

  BT

• Express12

  SSCoach

  Points: 18514

  More actions

  August 9, 2006 at 9:25 am

  #654114

  Got it..  I needed to ALTER TABLE DROP CONSTRAINT then ALTER TABLE ADD CONSTRAINT

  ALTER

  TABLE [dbo].[TAB_a] DROP CONSTRAINT [PK_c_Index_1]

  GO

  ALTER TABLE [dbo].[c_Index] ADD CONSTRAINT [PK_c_Index]

  PRIMARY KEY CLUSTERED

  ([COL_1] ASC, [COL_2] ASC, [COL_3] ASC)

  WITH FILLFACTOR = 80 ON [PRIMARY]

  BT