No relationships

mariomBrux
2009-06-22
2013-05-29
  • mariomBrux

    mariomBrux - 2009-06-22

    Hi everyone!

    I've read the FAQ (bullet 3) and the solutions offered on this forum regarding the tables relationship, but, for me, none of the solutions offered worked.

    All my 70 tables are displayed very well, on the "Utility Tables" tab, but at "Relationships" tab I have the following message
    "Generated by SchemaSpy on Mon Jun 22 16:56 CEST 2009    
    No relationships were detected in the schema. "

    Help, please!

    Thanks!

     
    • John Currier

      John Currier - 2009-06-22

      Just a couple of questions:

      What database are you using (and if using MySQL which engine)?

      Were there any error messages reported by SchemaSpy?

      John

       
      • mariomBrux

        mariomBrux - 2009-06-23

        Hi John! Thanks for the reply.

        I am using MySQL 5. More precisely 5.0.51b

        There were no reported error messages by SchemaSpy.

        M.

         
    • John Currier

      John Currier - 2009-06-23

      If you're using the MyISAM database engine under MySQL then no, SchemaSpy will not be able to tell you about the relationships because they don't exist.

      One possible solution to that issue is to switch the table definitions to InnoDB (normally a simple search/replace of your DDL).  Then create a temporary version of the database from that DDL and run SchemaSpy against it.

      Let me know if I've misunderstood something,
      John

       
      • mariomBrux

        mariomBrux - 2009-06-24

        Before writing on this forum I have checked the FAQ section and I have changed MyISAM to INNODB for the tables and database.

        I think I might do something wrong, but since there is no error when I compile it and all the tables are displayed perfectly (but with no relationship) I do not know what to correct.

         
        • John Currier

          John Currier - 2009-06-24

          I really don't know what could be wrong if you recreated the database after modifying the create table SQL to use InnoDB.  SchemaSpy should be able to detect the relationships if they really exist.

          One thing you can try is the beta version available at http://schemaspy.sourceforge.net/schemaSpy.jar
          There were some problems with how "no relationships" were handled that were resolved in the beta version.

          John

           
          • mariomBrux

            mariomBrux - 2009-06-25

            Hi John,

            Thanks again for the message. I tried the file you have sent me and I have the following results:
            - on the Tables tab for the columns Parents and Children there is nothing
            - in the Relationships tab there are some relationships, but they are not correct.

            Thanks again for all your efforts and the amazing job you are doing.

            I will try with a smaller7different database and see the results.

             
            • John Currier

              John Currier - 2009-06-25

              Are you positive that you really have foreign keys in your database?  That is, you should get some form of foreign key constraint violation if you try to insert an invalid foreign key into a table.

              Here's the DDL that I used to create the BOOK_LOCATION sample table:

              create table BOOK_LOCATION (
                isbn bigint,
                branchId integer not null,
                numCopies integer not null,
                primary key(isbn, branchId),
                foreign key(isbn) references BOOK(isbn),
                foreign key(branchId) references LIBRARY_BRANCH(branchId)
              ) type=InnoDB;

              Note that if I don't have "type=InnoDB" in there then I won't have any real relationships even though I have foreign keys defined.  I'll get an error if I try to insert a location into this table with an ISBN that isn't in BOOK.  If it allows it then there are no real relationships and SchemaSpy won't be able to report them.

              About the incorrect relationships:  are they represented with dashed lines?  If so then they were implied by their names/type/size matching the keys of other tables.  -noimplied can turn that off if desired.  If they're not implied then I'm not sure how SchemaSpy would be incorrectly interpreting your metadata.

              John

               
  • T Davis

    T Davis - 2009-11-25

    Having read the documentation at schemaspy.sourceforge.net, I pose the following stupid question: "Will SchemaSpy deduce the relationships between tables in the absence of primary and foreign keys?" My impression is that it can do so from the metadata.

    So far, the Relationships show up only when I run SS against a database with the PK/FK relationships already defined.

    I've tried the following combinations:

    SQL Server 2005 DB; Msft's SQL 2005 JDBC driver (sqljdbc_xa and
    sqljdbc.jar).

    SQL Server 2005 DB; Msft's SQL 2005 JTDS driver (jtds-1.2.jar).

    I've been using SchemaSpy GUI for convenience's sake.

    While the output has been superb and useful, my primary need is to to have an "inferred" relationship diagram for DBs without defined PK/FKs.

     
  • T Davis

    T Davis - 2009-11-25

    Ah. And I have used SS 4.1.1 as well as the "beta" listed in Post #6.

     
  • John Currier

    John Currier - 2009-11-30

    SchemaSpy can determine implied relationships if the column name and type match the column name and type of another table's primary key.  The beta also supports Ruby on Rails naming conventions for determining implied relationships.

    An alternative approach would be to use the -meta option to explicitly specify table relationships.

    John

     
  • T Davis

    T Davis - 2009-11-30

    John,

    Use case is …

    - Table1.Staff_Code (nvarchar; PK for Table1)
    - Table2.Staff_Code (nvarchar; PK for Table2)

    .

    Assuming I've not done something incorrectly, I expected SchemaSpy to show a relationship between Table1 and Table2, given Staff_Code was spelled the same in both tables and was the same data type.

    Does SchemaSpy pass over this case because they are both PKs?

     
  • John Currier

    John Currier - 2009-11-30

    I don't think it'd be able to determine an implied relationship for your example.  How would it know which was the implied foreign key?

    John

     
  • rapgame

    rapgame - 2011-02-06

    Hi,

    I have a firebird database, the example database provided with the package is giving a nice ERD with relationships.

    My database has none.

    This database is only maintained by myself and not designed.

    I think having 374 tables with no relationships is a bad idea.

    Schema Spy is not giving a relation, so it does not exsists or our programmer has a lousy design (d.i. no design??)

    Thanks, looking forward to your opinion on this.

     
  • John Currier

    John Currier - 2011-02-07

    If SchemaSpy isn't showing any relationships then there is either a problem in SchemaSpy or your database has no relationships that are reported by the JDBC metadata services.

    There are many philosophies around database design and there are many places on the web where database experts would love to critique your database.  I can make assumptions about the design based on your comments, but I'm not a database expert.

    John

     

Log in to post a comment.