Post reply

sp_rename of default constraint

  • March 17, 2016 at 3:30 pm

    #309134

    I have a table MyTable and in it I have a column with default constraint named DF_MyTable_MyColumn

    I renamed the table to MyTable_XYZ by:

    sp_rename 'MyTable', 'MyTable_XYZ'

    That worked great. Next I wanted to rename the constraint to DF_MyTable_XYZ_MyColumn and ran:

    sp_rename 'MyTable_XYZ.DF_MyTable_MyColumn', 'DF_MyTable_XYZ_MyColumn'

    The constrained didn't get renamed. All other constraint types worked fine (FK, CK, PK), except for default constraints. It also happened in SQL2008R2, SQL2012 and SQL2014.

    Is there a way around this, short of dropping the constraint and recreating it?

  • March 17, 2016 at 5:49 pm

    #1866039

    N_Muller (3/17/2016)

    I have a table MyTable and in it I have a column with default constraint named DF_MyTable_MyColumn

    I renamed the table to MyTable_XYZ by:

    sp_rename 'MyTable', 'MyTable_XYZ'

    That worked great. Next I wanted to rename the constraint to DF_MyTable_XYZ_MyColumn and ran:

    sp_rename 'MyTable_XYZ.DF_MyTable_MyColumn', 'DF_MyTable_XYZ_MyColumn'

    The constrained didn't get renamed. All other constraint types worked fine (FK, CK, PK), except for default constraints. It also happened in SQL2008R2, SQL2012 and SQL2014.

    Is there a way around this, short of dropping the constraint and recreating it?

    Replace the table name with the schema name of the constraint. From BOL...

    If the object to be renamed is a constraint, object_name must be in the form [font="Arial Black"]schema[/font].constraint.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

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