MySQL Bugs: #46979: 1 to 1 relationships do not set unique index on foreign key

Bug #46979	1 to 1 relationships do not set unique index on foreign key
Submitted:	28 Aug 2009 15:41	Modified:	28 Aug 2009 23:54
Reporter:	Marco Bernasocchi	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.1.16, 5.1.17	OS:	Any (ubuntu jaunty, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	1 to 1 relationships, foreign key

Description:
hi,
Designing a NON identifying 1:1 relationship between two tables (like user and user profile) WB does not set a UNIQUE index on the foreign key. 1:n relations are modeled in the exact same way (which AFAIK is correct).

In case of identifying 1:1 relations it might be an idea to have a warning if a PK is already defined in the referring table or to put a UNIQUE constraint as well.  

How to repeat:
For identifying:
create two tables one with PK the other without link them with 1:1 and 1:n relations

Suggested fix:
for 1:1 add unique constrain to FK or check that FK is the only member of PK (for identifying)

for 1:n (identifying) check that FK is not the only member of PK

Thank you for the bug report. Verified just as described with 5.1.17 on Mac OS X also.

Actually, looks like a duplicate of bug #43920.

Well, yes it kind of looked like this bug, but i decided to open a new report because i think that it is much more critical bug since it is a key fearure at db design time.