1

I just moved my Member table from MRK schema to GNR.

I previously had the script below to set its primary key:

If not Exists (select 1 
               from sys.objects 
               where name = 'PK_MRK_Member'
              )
        ALTER TABLE      [MRK].[Member] 
        ADD   CONSTRAINT [PK_MRK_Member] PRIMARY KEY CLUSTERED 
        (
          [MemberID] ASC
        ) ON [PRIMARY]
GO

How do I change the name AND the schema of the defined constraint now?

I tried exec sp_rename 'PK_MRK_Member', 'PK_GNR_Member', 'INDEX' for changing the name, but get this error:

Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.

P.S.: I cannot drop the table and/or the PK constraint and create them again since they PK is already in use by other tables.

arunbabu
  • 279
  • 3
  • 15
Yalda
  • 680
  • 1
  • 18
  • 39
  • 2
    you cannot alter constraint http://stackoverflow.com/questions/13244889/how-to-alter-constraint – Prasanth V J Oct 14 '14 at 08:42
  • Thanks for the help @PrasanthVJ and Jeroen Mostert I guess I will give up on that :) – Yalda Oct 14 '14 at 09:34
  • 1
    In general, you cannot alter a constraint. But this case is special -- although the primary key is a constraint, the underlying index _can_ be renamed, per @vladimir-semashkin below. – Jeroen Mostert Oct 14 '14 at 11:40

1 Answers1

3

As I see your first parameter is wrong for procedure sp_rename. When you rename an index you should specify the schema and table as well as index name. Check this link for example

Community
  • 1
  • 1
Vladimir Semashkin
  • 1,270
  • 1
  • 10
  • 21
  • 3
    In the code above, the statement that should do the rename is `exec sp_rename 'GNR.Member.PK_MRK_Member', 'PK_GNR_Member', 'INDEX'`. The schema of the index is always that of its parent table, so there's no need to rename that separately. – Jeroen Mostert Oct 14 '14 at 11:39