sql:need to change constraint on rename table?

Question

i have change name of table through procedure sp_rename.Do i need to change fk constraint of child table?

score 12 · Accepted Answer · answered Dec 24 '09 at 08:13

12

Constraints and indexes will be automatically renamed, but you will need to manually do rename work in stored procedures, triggers, user-defined functions, and views that reference the table. See the documentation on MSDN.

answered Dec 24 '09 at 08:13

Konamiman

49,681
17
108
138

1

Big thanks for saving me a morning's worth of unncessary work :-) – EvilDr May 18 '12 at 08:03
Not to nitpick but the constraints won't be renamed themselves, it's the references that will be adjusted. You can also update the constraint names with sp_rename. – Johan Maes Sep 13 '19 at 16:18

score 8 · Answer 2 · answered Dec 24 '09 at 08:11

No, the table name change will have also updated the apporpriate Metadata in the system catalogs and so the constraint will still be referencing the correct table.

You can use the following script to identify all foreign keys in order to validate your change should you wish.

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
FROM   SYS.ALL_OBJECTS O1,
       SYS.ALL_OBJECTS O2,
       SYS.ALL_COLUMNS C1,
       SYS.ALL_COLUMNS C2,
       SYS.FOREIGN_KEYS F
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
       INNER JOIN SYS.INDEXES I
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
             AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID

This script was sourced from: Identify all of your foreign keys in a SQL Server database

[here](http://stackoverflow.com/questions/8094156/know-relationships-between-all-the-tables-of-database-in-sql-server) is **another script** , hope helps someone. — Shaiju T, Jan 25 '16 at 13:22

sql:need to change constraint on rename table?

2 Answers2

Linked