Как программно определить тип отношения MySQL (1:1, 1:n, n:m) между таблицами?

Я пытаюсь запросить сервер MySQL, чтобы определить информацию о базе данных, чтобы создать некоторый код.

Я довольно успешно использовал Java JDBC и таблицу INFORMATION_SCHEMA для этого, но проблема в том, что мне нужно определить, является ли отношение таблицы OneToOne , OneToMany или ManyToMany. Я не могу найти хороший способ добиться этого, и мне бы очень хотелось, чтобы кто-нибудь мог мне немного помочь, и если это возможно, с решением, которое не является специфичным и надежным для MySQL, чтобы оно могло помочь другим.

Я нашел этот вопрос в stackoverflow, но он не решит проблему: как -для-определения-мощности-внешнего-ключа-использования-mysql

EDIT (More) Чтобы лучше объяснить мою проблему, я добавлю немного больше информации. В настоящее время я использую MySQL с InnoDB и MySQL Workbench для создания диаграммы EER и генерации SQL для создания базы данных.

Я пытаюсь перепроектировать в своем Java-приложении отношение между двумя существующими таблицами, чтобы определить, является ли таблица OneToOne, OneToMany или ManyToMany. Проблема в том, что когда я разрабатываю модель в MySQL Workbench и создаю связь между двумя таблицами, я не вижу никакой разницы между неидентифицирующим 1:1 и неидентифицирующим 1:N, даже если их выходные данные SQL одинаковы.

Не идентифицирующий 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

Неидентифицирующий 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

Удивительная часть начинается, когда я реверсирую базу данных с помощью MySQL Workbench, чтобы увидеть, может ли она угадать, была ли это 1:1 или 1:n. Угадайте, на диаграмме правильные стрелки отношений!!! Возможно, он где-то хранит ссылку как уникальную, или InnoDB имеет это в своей собственной INFORMATION_SCHEMA, специфичной для поставщика, но я хотел бы воспроизвести это поведение в своем приложении.

Любые идеи о том, как я могу этого достичь?


mysql sql cardinality relationship reverse-engineering
person Lethe    schedule 29.09.2014    source источник

Ответы (2)


arrow_upward
2
arrow_downward

После дальнейших исследований я обнаружил, что хотя каким-то образом MySQL Workbench может реконструировать отношение 1:1 и 1:n, даже если это неидентифицирующее отношение, в котором атрибут, ссылается на то, что внешний ключ не является PK или UQ, это может быть сделано для свойств, специфичных для поставщика (InnoDB).

ВСЕ другие протестированные инструменты обратного проектирования SQL показали неидентифицирующие отношения как OneToMany, даже если они были разработаны в MySQL WorkBench как неидентифицирующие OneToOne. Предполагая это, я предварительно сформировал запрос JOIN для получения необходимой информации, чтобы отличить 1: 1 от 1: n. Таким образом, SQL выглядит следующим образом:

Пример таблицы 1

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

Окончательно...

Псевдокод

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

Надеюсь, это поможет другим в борьбе, не стесняйтесь добавлять любые предложения или альтернативные способы сделать это, спасибо всем.

person Lethe    schedule 30.09.2014

arrow_upward
1
arrow_downward

Если у вас есть две таблицы следующим образом:

Table: a
a_id    unique autoincrement primary key
a_info  other information

а также

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

В этом дизайне по своей природе ноль, одна или несколько строк в b для каждой строки в a. По сути, в a для каждой строки в b есть одна строка. Именно это обычно подразумевается под OneToMany.

Если столбец a_id в таблице b имеет уникальный индекс, то в b для каждой строки a имеется либо ноль, либо одна строка. Именно это обычно подразумевается под OneToOne.

Если связь должна быть ManyToMany, вам потребуется дополнительная таблица, как показано ниже:

Table: a
a_id    unique autoincrement primary key
a_info  other information

и эта таблица

Table: b
b_id    unique autoincrement primary key
b_info  other information

связаны вместе этой таблицей

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

Эта таблица отношений реализует ManyToMany. Его уникальный первичный ключ представляет собой конкатенацию двух его столбцов.

Это довольно обычная конструкция; вы должны быть в состоянии выбрать это, когда вы реконструируете существующую схему. Что нужно искать, так это таблицы стилей отношений (a_b).

person O. Jones    schedule 29.09.2014
comment
Первоначально я думал, что основная видимая разница между OneToOne и OneToMany будет, как указал @OllieJones в предыдущем ответе, ссылкой на внешний ключ (в данном случае a_id в таблице b) помечен или помечен как UQ (уникальный), но, к моему удивлению, при использовании MySQL workbench с InnoDB я обнаружил, что и 1:1, и 1:N не t отмечен как UQ, и когда я реверсирую БД с помощью инструментов MySQL, ему каким-то образом удается обнаружить разницу между 1: 1 и 1: N. Я хотел бы знать, как это может быть обработано в этом сценарии. Спасибо за ваше время и усилия Олли Джонс - person Lethe; 29.09.2014