Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Script Out Foreign Keys With Multiple Keys

It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys.  But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?

For example,
--Script 1
create table dbo.fktable1(
  id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable1 primary key (id1, id2, id3))

create table dbo.fktable2(
  id int not null identity(1,1) primary key
, id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable1_fktable2] 
  FOREIGN KEY (id1, id2, id3) 
  REFERENCES dbo.fktable1 (id1, id2, id3))

Combining those multiple records in the sys.foreign_key_columns into a concatenated string in order to get this is tricky:

--Script 2
ALTER TABLE [dbo].[fktable2]  WITH CHECK 
  ADD  CONSTRAINT [FK_fktable1_fktable2] 
  FOREIGN KEY([id1], [id2], [id3])
  REFERENCES [dbo].[fktable1] ([id1], [id2], [id3])


Here's how I recently did this.   It actually turned out to be more complicated than I thought, certainly more complicated that your standard throw-strings-together-based-on-system-tables.  This is because we need to build a recurse of the multi-key values that are both referenced and referencing in foreign keys. 

--Script 3
WITH cteColumnNames_Base (
  FKName, ReferencingColumnNames, FKingRank,  ReferencedColumnNames, FKedRank)
   as    
  (      
  SELECT FKName                            =      f.name              
  ,             ReferencingColumnNames     =      CAST(c.name   as varchar(8000))              
  ,             FKingRank                         =      ROW_NUMBER() OVER (PARTITION BY f.Name ORDER BY c.column_id )              
  ,             ReferencedColumnNames      =      CAST(rc.name  as varchar(8000))              
  ,             FKedRank                          =      ROW_NUMBER() OVER (PARTITION BY f.Name ORDER BY rc.column_id)              
  FROM          sys.foreign_keys f              
  inner join    sys.objects o on f.parent_object_Id = o.object_id              
  inner join    sys.schemas s on o.schema_id = s.schema_id              
  inner join    sys.objects ro on f.referenced_object_Id = ro.object_id              
  inner join    sys.schemas rs on ro.schema_id = rs.schema_id              
  inner join    sys.foreign_key_columns fc on fc.constraint_object_id = f.object_id and fc.parent_object_id = o.object_id and fc.referenced_object_id = ro.object_id              
  inner join    sys.columns c on c.object_id = o.object_id and c.column_id = fc.parent_column_id              
  inner join    sys.columns rc on ro.object_id = rc.object_id and rc.column_id = fc.referenced_column_id       )
,  cteColumnNames_Concat (FKName, ReferencingColumnNames, ReferencedColumnNames, FKingRank, FKedRank )
  as     (SELECT 
  FKName              
  ,      ReferencingColumnNames              
  ,      ReferencedColumnNames              
  ,      FKingRank              
  ,      FKedRank       
  FROM cteColumnNames_Base       
  where FKingRank = 1       or FKedRank = 1       
  UNION ALL       
  SELECT 
  b.FKName              
  ,      c.ReferencingColumnNames + '], [' + b.ReferencingColumnNames              
  ,      c.ReferencedColumnNames + '], [' + b.ReferencedColumnNames              
  ,      b.FKingRank              
  ,      b.FKedRank       
  FROM cteColumnNames_Base b       
  INNER JOIN cteColumnNames_Concat c       on b.FKName = c.FKName      
 and (b.FKingRank = c.FKingRank + 1       or b.FKedRank = c.FKedRank + 1)       
  )
,  cteReferencingColumnNames (FKName, ReferencingColumnNames, TopRank) as 
  (SELECT 
    FKName             
  ,      ReferencingColumnNames              
  ,      TopRank                                  =      RANK() OVER (PARTITION BY FKName ORDER BY FKingRank Desc)       
  FROM  cteColumnNames_Concat c     
  )
,  cteReferencedColumnNames (FKName, ReferencedColumnNames, TopRank) as     
  (SELECT FKName              
  ,      ReferencedColumnNames              
  ,      TopRank                                  =      RANK() OVER (PARTITION BY FKName ORDER BY FKedRank Desc)       
  FROM  cteColumnNames_Concat c    
  )  
select distinct       
FKName = f.name
,      ReferencingTableName = s.name + '.' + o.name
,      ReferencingColumnName = '[' + con.ReferencingColumnNames + ']' 
,      ReferencedTableName  =      rs.name + '.' + ro.name
,      ReferencedColumnName = '[' + rcon.ReferencedColumnNames + ']' 
,      [TSQL] =  'ALTER TABLE [' + s.name + '].[' + o.name + ']  WITH CHECK 
ADD  CONSTRAINT [' + f.name + '] 
FOREIGN KEY([' + con.ReferencingColumnNames + '])
REFERENCES [' + rs.name + '].[' + ro.name + '] (['+rcon.ReferencedColumnNames+']);'
FROM          sys.foreign_keys f 
  inner join    sys.objects o on f.parent_object_Id = o.object_id 
  inner join    sys.schemas s on o.schema_id = s.schema_id
  inner join    sys.objects ro on f.referenced_object_Id = ro.object_id 
  inner join    sys.schemas rs on ro.schema_id = rs.schema_id
  inner join    sys.foreign_key_columns fc on fc.constraint_object_id = f.object_id and fc.parent_object_id = o.object_id and fc.referenced_object_id = ro.object_id
  inner join    sys.columns c on c.object_id = o.object_id and c.column_id = fc.parent_column_id
  inner join    sys.columns rc on ro.object_id = rc.object_id and rc.column_id = fc.referenced_column_id
  inner join    cteReferencingColumnNames con on con.FKName = f.Name and con.TopRank = 1
  inner join    cteReferencedColumnNames rcon on rcon.FKName = f.Name and rcon.TopRank = 1
order by f.name

And here's the results:


The right-most column (cut off for size purposes) produces the necessary TSQL to recreate the foreign key if necessary.  Compare to Script 2 above.


--Script 4
ALTER TABLE [dbo].[fktable2]  WITH CHECK  
ADD  CONSTRAINT [FK_fktable1_fktable2] 
FOREIGN KEY([id1], [id2], [id3])
REFERENCES [dbo].[fktable1] ([id1], [id2], [id3]);
Below is the complete test script set up to create several testing tables with various foreign key relationships: 

--Script 5
create table dbo.fktable1
( id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable1 primary key (id1, id2, id3)
)
create table dbo.fktable2
( id int not null identity(1,1) primary key
,      id1 int not null
, id2 int not null
, id3 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable1_fktable2] FOREIGN KEY (id1, id2, id3) REFERENCES dbo.fktable1 (id1, id2, id3)
)
create table dbo.fktable3
( id1 int not null
, id2 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable3 primary key (id1, id2)
)
create table dbo.fktable4
( id int not null identity(1,1) primary key
,      id1 int not null
, id2 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable3_fktable4] FOREIGN KEY (id1, id2) REFERENCES dbo.fktable3 (id1, id2)
)
create table dbo.fktable5
( id1 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable5 primary key (id1)
)
create table dbo.fktable6
( id int not null identity(1,1) primary key
,      id1 int not null
, text1 varchar(20) not null
, CONSTRAINT [FK_fktable5_fktable6] FOREIGN KEY (id1) REFERENCES dbo.fktable5 (id1)
)
create table dbo.fktable7
( id1 int not null
, text1 varchar(20) not null
, CONSTRAINT pk_fktable7 primary key (id1)
)
create table dbo.fktable8
( id int not null identity(1,1) primary key
, text1 varchar(20) not null
,      id2 int not null
, CONSTRAINT [FK_fktable7_fktable8] FOREIGN KEY (id2) REFERENCES dbo.fktable7 (id1)
)
create table dbo.fktable9
( id int not null identity(1,1) primary key
, text1 varchar(20) not null
,      id3 int not null
, CONSTRAINT [FK_fktable7_fktable9] FOREIGN KEY (id3) REFERENCES dbo.fktable7 (id1)
)
create table dbo.fktable10
( id int not null identity(1,1) primary key
, text1 varchar(20) not null
,      id4 int not null
,      id5 int not null
, CONSTRAINT [FK_fktable9_fktable10] FOREIGN KEY (id4) REFERENCES dbo.fktable9 (id)
, CONSTRAINT [FK_fktable7_fktable10] FOREIGN KEY (id5) REFERENCES dbo.fktable7 (id1)
)
create table dbo.fktable11
( id int not null identity(1,1) primary key
, text1 varchar(20) not null
,      id10 int not null
, CONSTRAINT [FK_fktable11_fktable10] FOREIGN KEY (id10) REFERENCES dbo.fktable10 (id)
)

--Test values
insert into dbo.fktable1 (id1, id2, id3, text1) values (1, 2, 3, 'test1'), (4, 5, 6, 'test2'), (7, 8, 9, 'test3')
insert into dbo.fktable2 (id1, id2, id3, text1) values (1, 2, 3, 'test1')
insert into dbo.fktable2 (id1, id2, id3, text1) values (4, 5, 6, 'test2')
insert into dbo.fktable3 (id1, id2, text1) values (1, 2, 'test1'), (4, 5, 'test2'), (7, 8, 'test3')
insert into dbo.fktable4 (id1, id2,  text1) values (1, 2,  'test1')
insert into dbo.fktable4 (id1, id2,  text1) values (4, 5,  'test2')
insert into dbo.fktable5 (id1,  text1) values (1,  'test1'), (4,  'test2'), (7,  'test3')
insert into dbo.fktable6 (id1, text1) values (1, 'test1')
insert into dbo.fktable6 (id1, text1) values (4, 'test2')
insert into dbo.fktable7 (id1,  text1) values (1,  'test1'), (4,  'test2'), (7,  'test3')
insert into dbo.fktable8 (id2, text1) values (1, 'test1')
insert into dbo.fktable8 (id2, text1) values (4, 'test2')
insert into dbo.fktable9 (id3, text1) values (7, 'test3')
insert into dbo.fktable9 (id3, text1) values (4, 'test2')
insert into dbo.fktable10 (id4, id5, text1) values (1, 4, 'test4')
insert into dbo.fktable10 (id4, id5, text1) values (2, 7, 'test7')
insert into dbo.fktable11 (id10,  text1) values (1,  'test10')
insert into dbo.fktable11 (id10,  text1) values (2,  'test11')


Comments

Leave a comment on the original post [www.sqltact.com, opens in a new window]

Loading comments...