2

There is a database online that I was able to download. It came in Access, I exported it to SQL Server. It works beautifully. However when I create a diagram, it doesn't show the relationships. By that I mean I would like to see a line connecting the foreign keys from each table.

My google searches have been unsuccessful. Some say select relationship labels. That does not work. I do know that if I created a database, the tables, the keys etc, I would see the line I am looking for. Any reason why they are not showing up in this particular case? I have tried in both SQL Server 2008 R2 on Win 7 and 2012 on win 8. Same problem.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dwarf
  • 445
  • 2
  • 9
  • 23
  • 3
    silly question but are the foreign keys really there? – John Sobolewski May 03 '13 at 13:10
  • Are you using SQL Server Management Studio? – Tim Lehner May 03 '13 at 13:11
  • 1
    I just pulled up a Northwind database with Orders and [Order Details], the relationship "linkers" are there. I agree, make sure there are actually FK in the database. – granadaCoder May 03 '13 at 13:15
  • If you are in SSMS you should be able to right click on a table and choose "Script table" as create to new query window and see the words "FOREIGN KEY" in the ALTER TABLE statements after the initial create table. If you don't see "FOREIGN KEY" you don't have foreign keys and that is why they don't show up in your database diagram – John Sobolewski May 03 '13 at 13:15

1 Answers1

3

Run this query to make sure some FK exists:

SELECT 
  [ForeignKey] = f.name
, [TableName] = OBJECT_NAME(f.parent_object_id), COL_NAME(fc.parent_object_id,fc.parent_column_id)
, [ReferenceTableName] = OBJECT_NAME (f.referenced_object_id)
, ReferenceColumnName = COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
FROM 
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • ok, so.. nothing came up, query executes quickly. i guess the contraints arent on there then? – dwarf May 03 '13 at 13:28
  • Yep. Now you know. And knowing is half the battle. – granadaCoder May 03 '13 at 13:35
  • so an older version of the database shows the relationships in access, export to sql server and no relationships. in the newest version, no relationships in either. strange – dwarf May 03 '13 at 13:39
  • I'm not familiar with the "upgrade Jet database to Sql Server".....are there any "advanced options" screens? You'd think FK's would come along for the ride. – granadaCoder May 03 '13 at 13:50
  • yes and its automatically selected. so im not quite sure. the database is located here: http://www.seanlahman.com/baseball-archive/statistics/ the 2010 version has the relationships in access, but not in sql server. the 2012 version has neither. the 2012 version sql script, doesnt work too well in sql server, back ticks, size, and even more problems after that. so i do the export from access to server, it works beautifully – dwarf May 03 '13 at 13:58