How to determine programmatically MySQL relationship type (1:1, 1:n, n:m) between tables?

Question

I'm trying to query a MySQL server to determine information about a database in order to scaffold some code.

I've been quite successful using Java JDBC and INFORMATION_SCHEMA table for this but the problem is that I need to determine if a table relation is OneToOne, OneToMany or ManyToMany. 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.

I found this question in stackoverflow but it wont solve the problem: how-to-determine-cardinality-of-foreign-key-using-mysql

EDIT (More) 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.

I'm trying to reverse engineer in my Java application the relation between two existing tables to determine if a table is OneToOne, OneToMany or ManyToMany. 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.

Non-Identifying 1:1

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

Non-Identifying 1:n

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

The amazing part comes when I do reverse the database using MySQL Workbench to see if it can guess if it was a 1:1 or a 1:n, 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.

Any ideas of how could I achieve this?

Answer 1

After further research I found that although somehow MySQL Workbench is able to reverse engineer the 1:1 and 1:n relation even when is a non-identifying relationship where the attribute that references the foreign key isn't PK or UQ, this might be do to vendor specific (InnoDB) properties.

ALL other SQL reverse engineer tools tested showed non-identifying relationships as OneToMany even thou they where designed in MySQL WorkBench as OneToOne non-identifying. Assuming this I preformed a JOIN query to retrieve the necessary information to distinguish 1:1 from 1:n So the SQL goes like the following:

Example for 'table1'

select INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
on INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME
where INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='table1' 
and referenced_table_name is not null

Finally...

Pseudo code

if (COLUMN_KEY == "PRI" || COLUMN_KEY == "UNI") { 
    //then you can assume is **OneToOne**
} else {
    //then you can assume is **OneToMany**
}

Hope this helps others with the struggle, feel free to add any suggestions or alternative ways of doing this, thanks all.

Answer 2

If you have two tables as follows:

Table: a
a_id    unique autoincrement primary key
a_info  other information

and

Table: b
b_id    unique autoincrement primary key
a_id    a reference to a row in a
b_info  other information

There are inherently, in this design, zero, one, or more rows in b for every row in a. There is inherently a single row in a for each row in b. This is what's usually meant by OneToMany.

If the column a_id in table b happens to have a unique index, then there is either zero or one row in b for each row in a. This is what's usually meant by OneToOne.

If the relationship is to be ManyToMany, you need an extra table, as follows:

Table: a
a_id    unique autoincrement primary key
a_info  other information

and this table

Table: b
b_id    unique autoincrement primary key
b_info  other information

related together by this table

Table: a_b
a_id    reference to a row in a
b_id    reference to a row in b

This relation table implements ManyToMany. Its unique primary key is the concatenation of its two columns.

This is a fairly conventional design; you should be able to pick it out when you're reverse-engineering an existing schema. The thing to look for is the relation (a_b) style tables.