112

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,782
  • 9
  • 52
  • 65
intrigued_66
  • 16,082
  • 51
  • 118
  • 189

7 Answers7

106

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
  • 5,098
  • 1
  • 50
  • 73
  • 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
85

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

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 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
54

You can use sp_rename.

sp_rename 'CK_Ax', 'CK_Ax1'
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 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
  • 1
  • 9
  • 15
7

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
  • 580
  • 1
  • 7
  • 17
  • 3
    And also: do not put the square brackets in the new name. Otherwise you might end up with a name containing brackets – casenonsensitive Jul 30 '20 at 09:46
  • You can also use `QUOTENAME()`. E.g.: `sp_rename 'schema.' + QUOTENAME('Name.With.Period.In.It'), 'New.Name.With.Period.In.It'`. Useful for when the object's name is in a variable rather than manually typed out. – br3nt Sep 20 '22 at 23:48
2

What about this?:

ALTER TABLE schema.table_name RENAME CONSTRAINT old_fk1 TO new_fk11;
NizMaS
  • 47
  • 3
0

If you having problem when run query to create table with error message "constraint already exist" like me, may be the cause is also like mine.

When our team updating db structure to production environment, we stuck because one new table cannot be create.

We found out that the table is actually an old table that has been renamed, thus the table already exist in production with the old name.

So your option is to rename the table in production.. Or delete it then create new table.

Ahmad Pujianto
  • 309
  • 1
  • 3
  • 11