I have a lot of tables that share keys but in the diagram does not show a route from table to table. Most tables will show a route to but a lot of them do not although they should. I've already checked data types, and I can join these tables, so I am wondering if this is something I have to do within the diagram tool to set the relationships to correlating tables. I assumed this is something sql server automatically does when you select tables for the diagram.
Any suggestions?
Edited: It could also just be that you need to "refresh" the diagram, remove and ad back the table to the diagram.
Original: The relationship is not shown in the diagram if this does not exist. Maybe you defined they key in both tables with same name and data type but you are missing the explicit reference (constraint). Something like this:
ALTER TABLE Sales.TempSalesReason
ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
REFERENCES Sales.SalesReason (SalesReasonID)
ON DELETE CASCADE
ON UPDATE CASCADE
;
You may want to ommit the ON [ACTION] CASCADE, though. The reason you can use JOIN in the queries is because the contraint is not mandatory in order to JOIN the tables, you can JOIN any columns as longs as the data type allows it. You can even JOIN with columns that don't have a PK, but this will have a bad performance (and is also another topic).
Refer to official documentation on how to do it with the graphic tool or with code:
I had the same behaviour because the diagram was opened twice with an older version. This was interfering with the current state set by scripts. Closing the old diagram solved the issue for me.
