How to: Rename foreign key constraint

• cm62597

  SSC Eights!

  Points: 976

  More actions

  April 15, 2010 at 9:17 am

  #139867

  ALTER TABLE dbo.a

  ADD FOREIGN KEY (id) REFERENCES dbo.b(id);

  How can change the name of this key....after running this command SQL creates a foreign key constraint called FK_4A03EDD9...Can I change this name to something that makes since?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 15, 2010 at 9:20 am

  #1152206

  it's the full syntax for a constraint instead of the shortcut for a foreign key:

  ALTER TABLE dbo.a

  ADD CONSTRAINT FK_MyForeignKey FOREIGN KEY (id) REFERENCES dbo.b(id);

  to rename a constraint that has a name you don't like,you use the proc sp_rename; it's like this:

  sp_rename 'FK__GMACT__AACCOMPLT__3C5683AE','FK_MY_ACCOMPLISH'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• WayneS

  SSC Guru

  Points: 95461

  More actions

  April 15, 2010 at 9:28 am

  #1152214

  You can also right-click the constraint (in SSMS), and select rename and do it there.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• PaulFenton

  SSC Veteran

  Points: 233

  More actions

  January 22, 2016 at 6:44 am

  #1853777

  Here's a script that I've used in the past to rename all constraints - it can handle Primary Keys, Foreign Keys, Defaults, Unique Constraints and Check Constraints.

  http://paulfentonsql.co.uk/2016/01/22/rename-all-constraints/%5B/url%5D