2

With reference to the last part of answer given here: What's the difference between identifying and non-identifying relationships?

A non-identifying relationship can be optional or mandatory, which means the foreign key column allows NULL or disallows NULL, respectively.

I am creating a non-identifying relationship in MySQL Workbench and whether I keep foreign key column MANDATORY or NON-MANDATORY, has no effect. Even if it is NON-MANDATORY I can't enter NULL values in it. I have to explicitly choose that particular foreign key as allowing NULL and only then I am able to store NULL values.
So I want to ask if this is the correct behaviour or this is a problem with MySQL Workbench or MySQL?

Thanks

Community
  • 1
  • 1
Ali
  • 1,801
  • 6
  • 43
  • 58

2 Answers2

0

This sounds like an issue with your design tool, which I'm not familiar with.

Foreign key constraints are always optional in SQL. That is, a foreign key doesn't require a row in the referencing table, it only requires that every referencing row has a corresponding one in the table being referenced (the parent table). This is one way in which ER modelling doesn't map well into a SQL database. You can draw a picture of a mandatory 1 to N (N>0) relationship in an ER diagram but for most practical purposes you cannot implement in SQL - it is effectively always 1 to 0/N.

Although nullable foreign keys are permitted I don't recommend you do it. Nulls in foreign keys are likely to cause problems with incorrect results later on, they work differently in different DBMSs and tend not to be properly understood even by expert users.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • This is the tool I am using which is provided by MySQL developer team. http://wb.mysql.com/?p=1043 – Ali Apr 07 '11 at 11:39
0

G'day,

Answer: Bugs in MySQL Workbench.

Solution: The workbench appears to get confused at times, I have noticed I need to firstly delete any existing relationships, and then manually remove artifacts left in the table from the relationship. Then re-create the relationship.

For a non-identifying relationship, workbench should indicate whether it is mandatory by a filled pink diamond icon (or not mandatory by an unfilled pink diamond). For an identifying relationship, you should be seeing a key to indicate a primary key.

I'm not complaining about bugs in the community edition though! the workbench software gets a 10/10 from me. If on the other hand you are running the standard edition, then contact MySQL.

(I'm running MySQL Workbench v5.2.34 CE, Rev 7780.)

Cheers

Sam
  • 116
  • 1
  • 1