Database Diagram Not Drawing Foreign Key Relations

Toad for SQL Server
1

Hi,

I must be doing something foolish, but I can’t figure out where I’m going wrong. Ordinarily, I rely on Database Diagram to visually map the FK relationships between tables. But recently I was experimenting with SQL Server’s treatment of cascading FKs with a simple example, and Toad didn’t draw any arrows at all.

Here is the code. When I try to drag table1 to Database Diagram, it automatically pulls in table2 but does not draw the FK arrow between table2.lastModifiedBy and table1.user_ID:

CREATE TABLE table1

(

user_ID INTEGER NOT NULL PRIMARY KEY,

user_name **CHAR** (**50**) NOT NULL

)

GO

CREATE TABLE table2

(

author_ID **INTEGER** NOT NULL PRIMARY **KEY**,

author_name **CHAR** (**50**) NOT NULL,

lastModifiedBy **INTEGER** NOT NULL,

addedby **INTEGER** NOT NULL

)

GO

ALTER TABLE table2 ADD CONSTRAINT fk_one FOREIGN KEY (lastModifiedBy)

REFERENCES table1 (user_ID) ON DELETE CASCADE ON UPDATE CASCADE

GO

2

Hi,

Just created these two tables and send table1 to diagram. Works like a charm for me:

toad_diagram_01.png

Have you customized settings around diagrammer?

3

Hi Valentine,

Thank you for checking this for me. I continued to have problems making the Database Diagram draw the FK arrow, but since you had success I restarted Toad and the arrow is now showing … but only if I had not previously generated a Database Diagram.

This is my sequence for reproducing the bug on 6.0.0.323:

  1. Run the above code to generate table1, table2, and fk_one

  2. Drag table1 to the Database Diagram which will automatically pull up table2 and draw the fk_one arrow

  3. Run the same code but renaming table3, table4, and fk_two

  4. Try dragging table3 to the Database Diagram. table4 is not automatically found, and even if you manually drag table4 to the same diagram, the fk_two arrow is not drawn.

**Crucially, I see this misbehavior in 6.0.0.323, but not in Beta 6.1.0.73. ** To the best of my knowledge, the beta’s setup is a mirror image of the production version.

Best,

KJ

4

Thanks for additional info.

BTW what’s version of Sql Server you are connected to?

5

SQL Server 2012 Service Pack 1, 11.00.3000.00 (x64).

  • KJ