Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Changing Foreign key names to standard naming convention across the DB Expand / Collapse
Author
Message
Posted Wednesday, February 10, 2010 5:40 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 11, 2014 8:07 PM
Points: 891, Visits: 235
Comments posted to this topic are about the item Changing Foreign key names to standard naming convention across the DB


Post #863746
Posted Wednesday, February 24, 2010 12:44 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 646, Visits: 1,852
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.
Post #871757
Posted Wednesday, February 24, 2010 7:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869, Visits: 963
You could just add the column name into the new FK definition. That would make it unique.
Post #871903
Posted Wednesday, February 24, 2010 11:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 11, 2014 8:07 PM
Points: 891, Visits: 235
Thanks for the feedback. I will incorporate the inputs.

Regards,
Vidhyadhar



Post #872488
Posted Friday, February 26, 2010 3:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 11, 2014 8:07 PM
Points: 891, Visits: 235
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.




Post #873293
Posted Friday, February 26, 2010 7:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869, Visits: 963
Vidya - can you give an example of what you are referring to? Thanks.
Post #873407
Posted Friday, February 26, 2010 7:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 646, Visits: 1,852
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.
Post #873408
Posted Friday, February 26, 2010 7:46 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 646, Visits: 1,852
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
Post #873429
Posted Friday, February 26, 2010 7:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869, 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.
Post #873439
Posted Friday, February 26, 2010 7:57 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 646, Visits: 1,852
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]'
Post #873448
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse