Changing Foreign key names to standard naming convention across the DB

  • Comments posted to this topic are about the item Changing Foreign key names to standard naming convention across the DB

  • Thanks for the script. 🙂

    It's a good idea, but potentially you could get duplicates, where you have multiple foreign keys between the same 2 tables.

    But those could easily be addressed manually as that part of the script would fail.

  • You could just add the column name into the new FK definition. That would make it unique.

  • Thanks for the feedback. I will incorporate the inputs.

    Regards,

    Vidhyadhar

  • We can think of adding column names into foreign key names. But it may not be better if there are multiple columns involved in the relationship.

    If you have multiple relationship keys between same 2 tables, I would suggest to change the script manually to change the foreign key names to FK1_, FK2_ etc.

  • Vidya - can you give an example of what you are referring to? Thanks.

  • Chris,

    A foreign key isn't necessarily between 1 field in each table. If you have a composite primary key made up of 4 fields, for example, then a foreign key on this table will have four fields on each side.

    Is this what you wanted an explanation of?

    Regards,

    David.

  • Below is my CTE effort, which won't work on SQL2000, but which does consider multiple fkeys.

    I'm not entirely sure what the 'standard' convention is, as it seems to depend on how you create your foreign keys. I think it might even have changed between releases / service packs. But the essential elements have remained...i.e...pktable, fktable and numeric counter. The script below should be quite simple to customise to taste.

    Regards,

    David.

    ;with keycouples as

    (

    SELECT fkeyid, rkeyid, count(*) as keycount

    FROM sys.sysreferences

    GROUP BY fkeyid, rkeyid

    ),

    referencerows as

    (

    select

    refs.constid as constid, refs.fkeyid, refs.rkeyid, row_number() over (partition by refs.fkeyid,refs.rkeyid order by refs.fkeyid , refs.rkeyid) as keynum, coup.keycount

    FROM sys.sysreferences refs INNER JOIN keycouples coup on refs.fkeyid = coup.fkeyid and refs.rkeyid = coup.rkeyid

    ),

    chunks as

    (

    select

    quotename(quotename(object_schema_name(rr.fkeyid))+'.'+quotename(object_name(rr.constid)),'''') as objname,

    object_name(rr.rkeyid)+'_'+object_name(rr.fkeyid) +case when rr.keycount=1 then '' else '_FK'+cast(rr.keynum as varchar) end as newname

    FROM referencerows rr

    )

    SELECT 'exec sp_rename ' + objname + ','+quotename(newname,'''')+','+quotename('OBJECT','''')+' GO' FROM chunks

    order by objname

  • I was actually looking for Vidya to elaborate on his alternate naming convention. Would FK1_, FK2_, etc..., still just contain the table names? Do you prefer this over using column names because the definition can become long, especially in the case of composite keys? Just curious.

  • I assume he's talking about a variation on the following, which is what SQL Server will create for you, if you create your fks from a diagram for example (which is what I usually do.)

    Here the 2 table names are present, and just an incremental number for the FK counter.

    If you include field names, you could find yourself on the wrong side of 255 character limit.

    '[dbo].[Custodian_SubPortfolio_FK1]'

    '[dbo].[Custodian_SubPortfolio_FK2]'

    '[dbo].[Dimension_SubPortfolio_FK1]'

    '[dbo].[Dimension_SubPortfolio_FK2]'

    '[dbo].[Dimension_SubPortfolio_FK3]'

    '[dbo].[FK_LeadPortfolio_SubPortfolio]'

    '[dbo].[FK_SubPortfolio_FundManager]'

    '[dbo].[InvestmentManager_LeadPortfolio_FK1]'

    '[dbo].[LegalEntity_LeadPortfolio_FK1]'

    '[dbo].[Mandate_SubPortfolio_FK1]'

    '[dbo].[ProductManager_LeadPortfolio_FK1]'

    '[dbo].[ThirdPartyA_SubPortfolio_FK1]'

    '[dbo].[TransferAgency_LeadPortfolio_FK1]'

  • Hi,

    I was trying to reply the Posts by David McKinney and chrs-513176 in the same thread.

    Where he is talking about rare scenario of having mutiple foreign keys between Table1 and Table2.

    For Example-

    Table1

    Column1 int not null Primary key

    Column2 int not null Unique Key

    Table2

    Column1 int references table1.column1

    Column2 int references table1.column1

    Here we can not go with the naming the foreign key like

    FK_Table2_Table1, becauase there are two keys. Name duplication will become the issue

    Here we can follow names like

    FK1_Table2_Table1

    FK2_Table2_Table1

    As a second option we can add column name(s) along with FK_Table2_Table1, but key name may become lengthy if the keys are composite.

  • This is an interesting topic, which needs further discussion. As an example I have several tables that have duplicated foreign keys to the same relationship. When resolving system generated foreign keys into something more developer/support friendly, there are two options.

    1.FK_ForeignTableName_ForeignColumnName(s)_PrimaryTableName_PrimaryColumnName(s) (SQL-92, so some degree)

    Or in the parent child order of:

    2.FK_PrimaryTableName_PrimaryColumnName(s) _ForeignTableName_ForeignColumnName(s)

    The standard doesn't seem to be well established for SQL Server if you want to name your own foreign keys, so from my perspective, this must be decided within the organization or enterprise. I have never liked the system generated method and the number one method above is more to SQL-92 standards. I personally like the second option, for it has a natural flow.

    The problem I have had is the renaming of the system established foreign keys is best resolved using 4-points names Option 1 or 2 above. Failure to use something that has just three qualifiers will result in duplicate names for foreign keys and thus be un-usable.

    I welcome any return comments.

    Hank Freeman
    Senior SQL Server DBA / Data & Solutions Architect
    hfreeman@msn.com
    678-414-0090 (Personal Cell)

Viewing 12 posts - 1 through 11 (of 11 total)

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