2

Using MySQL 8

I need to import in LucidChart the ER extracted from a database.

The following is the query they ask to exec to get the rows with all the info they need for the import. Unfortunately, this is not working as the only keys I can see are the primary keys. I tried to understand what's wrong and why is messing up with the other keys but I can't figure it out.

 SELECT 'mysql' dbms
      , t.TABLE_SCHEMA 
      , t.TABLE_NAME 
      , c.COLUMN_NAME
      , c.ORDINAL_POSITION
      , c.DATA_TYPE
      , c.CHARACTER_MAXIMUM_LENGTH
      , n.CONSTRAINT_TYPE
      , k.REFERENCED_TABLE_SCHEMA
      , k.REFERENCED_TABLE_NAME
      , k.REFERENCED_COLUMN_NAME 
   FROM INFORMATION_SCHEMA.TABLES t 
   LEFT 
   JOIN INFORMATION_SCHEMA.COLUMNS c 
     ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
    AND t.TABLE_NAME = c.TABLE_NAME 
   LEFT 
   JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
     ON c.TABLE_SCHEMA = k.TABLE_SCHEMA 
    AND c.TABLE_NAME = k.TABLE_NAME 
    AND c.COLUMN_NAME = k.COLUMN_NAME 
   LEFT 
   JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n 
     ON k.CONSTRAINT_SCHEMA = n.CONSTRAINT_SCHEMA 
    AND k.CONSTRAINT_NAME = n.CONSTRAINT_NAME 
    AND k.TABLE_SCHEMA = n.TABLE_SCHEMA 
    AND k.TABLE_NAME = n.TABLE_NAME 
  WHERE t.TABLE_TYPE = 'BASE TABLE' 
    AND t.TABLE_SCHEMA NOT IN('INFORMATION_SCHEMA','mysql','performance_schema');

The key info is that I'm using Laravel migrations. I think I already found the answer but I'll leave it open.

Karl Hill
  • 12,937
  • 5
  • 58
  • 95
Luca Giardina
  • 478
  • 4
  • 14
  • Is your goal to create an ER diagram from an existing mysql database? If so, then MySQL Workbench will do that for you. – Honeyboy Wilson Aug 28 '20 at 16:04
  • Interestingly i can see all the foreign-key too when i ran this query on my database. Do you have foreign key relation in your database ? – CaffeinatedCod3r Aug 28 '20 at 16:06
  • @CaffeinatedCod3r of course I have.. if I run the query just joining the last tables I will get the list and that's what I did, manually. – Luca Giardina Aug 29 '20 at 16:53
  • @HoneyboyWilson thanks I know, I needed to do something else – Luca Giardina Aug 29 '20 at 16:53
  • You said you get the list by joining the tables, but in the question you said you could only see primary key. Maybe i am missing something here? – CaffeinatedCod3r Aug 29 '20 at 16:57
  • with the full query I get the full list but CONSTRAINT_TYPE has only "PRIMARY_KEY" and NULL values. If I join just KEY_COLUMN_USAGE and TABLE_CONSTRAINTS I get everything, PRIMARY KEY, FOREIGN KEY and UNIQUE. – Luca Giardina Aug 29 '20 at 17:01
  • 1
    Can you provide an example where this query provides an incorrect result (as it is a bit hard to pinpoint your exact problem)? E.g. the output of `show create table sometable` and the result you get for this table with your query (e.g. add a `and t.TABLE_NAME = 'sometable'` to your query to limit it to the sample table) as well as the result you expect this query should give you. – Solarflare Sep 01 '20 at 00:15
  • 1
    Create some sample fiddle (3-4 tables with primary/secondary/foreign unique/common single/composite keys). Show desired output for this sample fiddle. – Akina Sep 01 '20 at 07:34

0 Answers0