title: How to determine programmatically MySQL relationship type (1:1, 1:n, n:m) between tables?
body: <p>I'm trying to query a MySQL server to determine information about a database in order to scaffold some code.</p>
  
  <p>I've been quite successful using <strong>Java JDBC</strong> and <strong>INFORMATION_SCHEMA</strong> table for this but the problem is that I need to determine if a table relation is <strong>OneToOne</strong>, <strong>OneToMany</strong> or <strong>ManyToMany</strong>. I can't find a good way to achieve this and I would love if someone could help me a bit and if its possible with a solution that is not MySQL specific and solid so it could help others.</p>
  
  <p>I found this question in stackoverflow but it wont solve the problem:
  <a href="https://stackoverflow.com/questions/12618285/how-to-determine-cardinality-of-foreign-key-using-mysql">how-to-determine-cardinality-of-foreign-key-using-mysql</a></p>
  
  <p><strong>EDIT (More)</strong>
  To further explain my problem I will add a bit more information. Currently I'm using MySQL with InnoDB and MySQL Workbench to create the EER Diagram and generate the SQL to create the database.</p>
  
  <p>I'm trying to reverse engineer in my Java application the relation between two existing tables to determine if a table is <strong>OneToOne</strong>, <strong>OneToMany</strong> or <strong>ManyToMany</strong>. The problem is that when I design the model in MySQL Workbench and I create a relationship between two tables I don't see any difference between Non-Identifying 1:1 and Non-Identifying 1:N even their SQL output are the same.</p>
  
  <p><strong>Non-Identifying 1:1</strong></p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `TestDB`.`table1` (
    `var1` BIT(1) NOT NULL,
    `var2` BIT(8) NOT NULL,
    `var3` VARCHAR(45) NULL DEFAULT NULL,
    `var4` INT(11) NOT NULL,
    `table2_var1` INT(11) NOT NULL,
    PRIMARY KEY (`var1`, `var2`),
    INDEX `fk_table1_table2_idx` (`table2_var1` ASC),
    CONSTRAINT `fk_table1_table2`
      FOREIGN KEY (`table2_var1`)
      REFERENCES `TestDB`.`table2` (`var1`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8
  COLLATE = utf8_general_ci
  </code></pre>
  
  <p><strong>Non-Identifying 1:n</strong></p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `TestDB`.`table1` (
    `var1` BIT(1) NOT NULL,
    `var2` BIT(8) NOT NULL,
    `var3` VARCHAR(45) NULL DEFAULT NULL,
    `var4` INT(11) NOT NULL,
    `table2_var1` INT(11) NOT NULL,
    PRIMARY KEY (`var1`, `var2`),
    INDEX `fk_table1_table2_idx` (`table2_var1` ASC),
    CONSTRAINT `fk_table1_table2`
      FOREIGN KEY (`table2_var1`)
      REFERENCES `TestDB`.`table2` (`var1`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8
  COLLATE = utf8_general_ci
  </code></pre>
  
  <p>The amazing part comes when I do reverse the database using MySQL Workbench to see if it can guess if it was a <strong>1:1</strong> or a <strong>1:n</strong>, it's actually able to guess it, the diagram has the correct relationship arrows!!! Perhaps it stores the reference as unique somewhere or InnoDB has this on its own vendor specific INFORMATION_SCHEMA but I would like to replicate this behaviour on my application. </p>
  
  <p>Any ideas of how could I achieve this?</p>