How to: Rename foreign key constraint

Question

Post reply

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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: 95460 More actions · Answer 2

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 · Answer 3

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/[/url]