Post reply

How to: Rename foreign key constraint

  • 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?

  • 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!

  • 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

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

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

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