Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing Foreign key names to standard naming convention across the DB


Changing Foreign key names to standard naming convention across the DB

Author
Message
vidya_pande
vidya_pande
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 242
Comments posted to this topic are about the item Changing Foreign key names to standard naming convention across the DB



David McKinney
David McKinney
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 2090
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
cy-dba
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 963
You could just add the column name into the new FK definition. That would make it unique.
vidya_pande
vidya_pande
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 242
Thanks for the feedback. I will incorporate the inputs.

Regards,
Vidhyadhar



vidya_pande
vidya_pande
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 242
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
cy-dba
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 963
Vidya - can you give an example of what you are referring to? Thanks.
David McKinney
David McKinney
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 2090
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
David McKinney
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 2090
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
cy-dba
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 963
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
David McKinney
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 2090
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]'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search