Syntax for renaming a foreign key with sp_rename

Assuming the following tables:

CREATE TABLE Shopping.Product (CategoryID INT NOT NULL)
CREATE TABLE Shopping.Category (CategoryID INT PRIMARY KEY)

ALTER TABLE Shopping.Product
    ADD CONSTRAINT FK_Product_Caetgory FOREIGN KEY(CategoryID)
    REFERENCES Shopping.Category

There is a typo in the foreign key name. How would you fix this using sp_rename? Is it…

-- 1. Just the object name as seen in sys.objects:
sp_rename 'FK_Product_Caetgory', 'FK_Product_Category', 'OBJECT'

-- 2. Qualified with  the schema:
sp_rename 'Shopping.FK_Product_Caetgory', 'FK_Product_Category', 'OBJECT'

-- 3. Qualified with the schema and table:
sp_rename 'Shopping.Product.FK_Product_Caetgory', 'FK_Product_Category', 'OBJECT'

If you picked #2, you are correct. This took me 10 mins to figure out.

8 thoughts on “Syntax for renaming a foreign key with sp_rename

  1. if prefer this documented way:
    exec sp_rename ‘Chronos.OnlineHotels.PK_OnlineHotelsToday’,’PK_OnlineHotels’,’Index’

  2. sp_rename ‘lineplan.proto_main.FK__proto_mai__prodc__15DA3E5D’,’FK_proto_prodclass’, ‘OBJECT’

    Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321
    Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong

Comments are closed.