100

Is it possible to rename a constraint in SQL Server? I don't want to have to delete and create a new one because this constraint affects other already existing constraints and I will have to recreate/alter those.

Andre Silva
  • 4,693
  • 9
  • 49
  • 64
mezamorphic
  • 14,525
  • 48
  • 112
  • 173

5 Answers5

94

After some more digging, I found that it actually has to be in this form:

EXEC sp_rename N'schema.MyIOldConstraint', N'MyNewConstraint', N'OBJECT'

Source

ozz
  • 4,773
  • 50
  • 71
  • 7
    This is the more precise answer. The other forms only work because most people dump all their tables into the default schema (usually "dbo:) and you can omit the default schema. But if you use multiple schemas, this is correct (and you can always state the "dbo"). – Godeke Feb 27 '17 at 17:19
  • From the source, doesn't look like you need the N'OBJECT' reference – Abrham Smith Aug 05 '20 at 13:18
82

You can rename using sp_rename using @objtype = 'OBJECT'

This works on objects listed in sys.objects which includes constraints

gbn
  • 408,740
  • 77
  • 567
  • 659
  • 16
    Just in case you can't get this to work, it says in MSDN - *"When renaming a constraint, the schema to which the constraint belongs must be specified."* – Black Light Oct 11 '13 at 10:41
  • 1
    For PK constraints there's no need for `@objtype = 'OBJECT'` to be specified. Just specify old name and new name. – pkuderov Feb 27 '17 at 23:08
53

You can use sp_rename.

sp_rename 'CK_Ax', 'CK_Ax1'
Mikael Eriksson
  • 132,594
  • 21
  • 199
  • 273
  • 2
    +1 This is what SSMS uses when renaming constraints. For a PK constraint it passes `INDEX` as object type. – Martin Smith Jan 03 '12 at 13:24
  • 3
    It's also important to note that CHECK constraints, unlike PKs, do not require a table prefix on the first `sp_rename` parameter, and will fail if you use one. – mattmc3 Jan 10 '17 at 16:52
8

answer is true :

exec sp_rename 
@objname = 'Old_Constraint',
@newname = 'New_Constraint',
@objtype = 'object'
rojib
  • 347
  • 9
  • 14
5

I know this is an old question, but I just found the following to be very helpful, in addition to the other great answers:

If the constraint to be renamed has a period in it (dot), then you need to enclose it in square brackets, like so:

sp_rename 'schema.[Name.With.Period.In.It]', 'New.Name.With.Period.In.It'
Winks
  • 417
  • 1
  • 6
  • 16