Changing Foreign key names to standard naming convention across the DB

Question

Changing Foreign key names to standard naming convention across the DB

vidya_pande

SSCertifiable

Points: 5275
More actions
February 10, 2010 at 5:40 pm

#386296

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

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

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

David McKinney SSChampion Points: 10472 More actions · Answer 1

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.

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 2

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

vidya_pande SSCertifiable Points: 5275 More actions · Answer 3

Thanks for the feedback. I will incorporate the inputs.

Regards,

Vidhyadhar

vidya_pande SSCertifiable Points: 5275 More actions · Answer 4

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.

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 5

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

David McKinney SSChampion Points: 10472 More actions · Answer 6

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.

David McKinney SSChampion Points: 10472 More actions · Answer 7

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

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 8

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.

David McKinney SSChampion Points: 10472 More actions · Answer 9

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]'

vidya_pande SSCertifiable Points: 5275 More actions · Answer 10

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.

hfreeman Mr or Mrs. 500 Points: 517 More actions · Answer 11

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)