MySQL Bugs: #43920: one to one relationship not recognized when import

Bug #43920	one to one relationship not recognized when import
Submitted:	27 Mar 2009 18:10	Modified:	16 Oct 2009 11:32
Reporter:	vicente san silvestre	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.30	OS:	Windows (xp 32 bits)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any
Tags:	CHECKED, error, one to one relationship, reverse engineer

Description:
one to one relationship error  when i use  import->reverse engineer script

hi im having a problem with "one to one" relationships changing into "one to many" relationships 

How to repeat:
1: create a one to one relationship between 2 tables (using workbench)
verify that the relationship is marked as one t one

user
-------------------------------
user_id ->      nn, ai, pk
user_type_id -> nn, fk
-------------------------------

user_type
-------------------------------
user_type_id -> nn, ai, pk
-------------------------------

2:Select: File->Export->Forward Engineer SQL create script

3:Select the output file path for file. (test.sql)*

4:Open a new project on Workbench

5:Select: File->Import->Reverse Engineer

6: Select the input file (test.sql)*

7:see the relationship type defined is changed to one to many

this is a big problem if you use  JPA and you generate the entities with some tool and not manually  pls fix the bug so we programmers dont have to do this boring job manually thanks

email: lema017@hotmail.com

Suggested fix:
none

Thank you for the problem report. Indeed, relashionship is just *saved* as 1-to-N:

CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL ,
  `table2_idtable2` INT NOT NULL ,
  PRIMARY KEY (`idtable1`, `table2_idtable2`) ,
  INDEX `fk_table1_table2` (`table2_idtable2` ASC) ,
  CONSTRAINT `fk_table1_table2`
    FOREIGN KEY (`table2_idtable2` )
    REFERENCES `mydb`.`table2` (`idtable2` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

For 1-to-1 I'd expect UNIQUE constraint added for the FOREIGN KEY column...

Bug #46979 was marked as a duplicate of this one.

Many thanks for writing a bug report.

Unfortunately, this is not a bug.

Consider 1:n with n>0. And 1 is a value that is higher then 0.

When our server or better every single storage engine would support by automatism a difference between 1:n and 1:1 then we got tons of feature request to implement 1:2 or 1:3 or 1:42.

When you want to have 1:1 relation then on server you need to say:
CREATE TABLE t(i integer, primary key(i));
CREATE TABLE t1(i integer, j integer UNIQUE, primary key(i), foreign key(j) references t(i));

As you see you need to set the unique constraint manually here too. If you didn't set the unique constraint the server don't know if it is 1:1 or 1:2 or 1:234567.

Same vise versa in Workbench ... it is necessary, that when you set 1:1 relation to set unique constraint too.

That we made an additional button here for 1:1 is just for the graphic not for the SQL.

I forgot to say that there are tons of users outside who would not like when we do the UNIQUE constraint by automatism because this means an additional index and more worse performance on DML.

Lots of our users don't want the database to check this by automatism.

I see your point of view, but isn't a 1:1 relationship kind of special (it has a special button)? I think that it should at least be a documented behaviour (since the special button makes me think of a correctly working behaviour).
cheers Marco

>> That we made an additional button here for 1:1 is just for the graphic not for the SQL.

Well, if I understend this right there is only a "button 1:1" for graphic and if I really want this behaviour I must add unique index manually? If so, I really can't see your logic, sorry ;)

This bug needs to be reopened:
- it's not true that handling 1:11 entails having to cater to 2:1, 3:1 etc, there's a reason the UNIQUE keyword exists and not the DOUBLE or TRIPLE keywords
- Once Workbench generates a CREATE script, I can (and have to) manually tweak the generated FOREIGN KEY index to prefix it with the aforementioned UNIQUE keyword.

So there's a visual option to represent a 1:1 relationship, and there's a perfectly valid (accepted by the MySQL server) DDL representation for it. All that's missing is the option to generate the latter from the former in Workbench.

This bug has to be reopened. or at-least notify in the mysql workbench when there such case. Everyone above have said everything that affects the developer; as for me it's very confusing to reverse engineer a database with a one-to-one relationship and see a one to many on the er-diagram. Especially when developing from code first approach.