CODEWITHSUNDEEP
mysqljetbrains-idedatagrip
TZK203
TZK203

Reputation: 167

DataGrip diagram not showing table relationships

I have been having this odd issue where my DataGrip diagram generations do not showcase the relationships between tables (in other words, the relationship arrows do not appear).

Here is an image of what I am talking about.

I have been making this DataGrip diagram as such: Right click on my schema --> Diagrams --> Show Visualization. The above image is the result every time.

How can I fix this to show the arrows? And before you ask, I have foreign keys in my schema tables that reference other columns in other tables.

Upvotes: 7

Views: 6995

Answers (2)

Svic321
Svic321

Reputation: 33

I was facing a similar problem. For me, everything worked when using postgresSQL as a database, but when I switched to MYSQL, and after executing the same queries to create the tables in postgresSQL, the relation between the tables weren't showing any more. I used the Diagramm tool from DataGrip to visualize it.

Then I compared the data types of the tables between my two databases: postgresSQL and MYSQL, I then realized that at the moment when creating my primary key, I used the SERIAL data type, and postgresSQL sets it as INTEGER data type, while MYSQL sets it as BIGINT UNSIGNED.

The queries I used to create the tables in postgresSQL are:

CREATE TABLE account(
    user_id     SERIAL          PRIMARY KEY,
    username    VARCHAR(50)     UNIQUE NOT NULL,
    password    VARCHAR(50)     NOT NULL,
    email       VARCHAR(250)    UNIQUE NOT NULL,
    created_on  TIMESTAMP       NOT NULL,
    last_login  TIMESTAMP
);
CREATE TABLE job(
    job_id      SERIAL          PRIMARY KEY,
    job_name    VARCHAR(200)    UNIQUE NOT NULL
);
# now a table which connects the two above
CREATE TABLE account_job(
    user_id     INTEGER     REFERENCES  account(user_id),
    job_id      INTEGER     REFERENCES  job(job_id),
    hire_date   TIMESTAMP
);

This query executes without errors while using PostgresSQL and MYSQL, but for MYSQL, the relation between the tables won't display or won't work.

I had to change the last command of the query as follow:

CREATE TABLE account_job(
    user_id     BIGINT UNSIGNED,
    FOREIGN KEY (user_id)   REFERENCES account(user_id),
    job_id      BIGINT UNSIGNED,
    FOREIGN KEY (job_id)    REFERENCES job(job_id),
    hire_date   TIMESTAMP
);

Note that the syntax of the command is different and that the data type is no longer INTEGER, but BIGINT UNSIGNED.

After doing these changes, my problem was solved. I hope this helps anyone facing the same problem.

Upvotes: 0

M.Kasaei
M.Kasaei

Reputation: 328

Make sure that foreign keys are declared properly.

check the column status in database details view on the left side.

If you are using mysql 8 , you might need to add ENGINE = INNODB after creating any table.

for example :

CREATE TABLE person
(
....
) ENGINE = INNODB;

Upvotes: 1

Related Questions