非识别关系中的强制性和非强制性
参考这里给出的答案的最后一部分： 识别关系和非识别关系有什么区别？
非识别关系可以是 可选或强制，这意味着 外键列允许 NULL 或 分别不允许 NULL。
我正在 MySQL Workbench 中创建非标识关系，无论我将外键列保留为强制还是非强制，都没有效果。即使它是非强制性的，我也不能在其中输入 NULL 值。我必须明确选择该特定外键作为允许 NULL，只有这样我才能存储 NULL 值。
所以我想问这是正确的行为还是MySQL Workbench或MySQL的问题？
谢谢
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您好，
答案：MySQL Workbench 中的错误。
解决方案：工作台有时看起来很混乱，我注意到我需要首先删除任何现有关系，然后从关系中手动删除表中留下的工件。然后重新建立关系。
对于非识别关系，工作台应通过填充的粉红色菱形图标指示是否是强制的（或者通过未填充的粉红色菱形图标不是强制的）。对于标识关系，您应该看到一个表示主键的键。
不过，我并不是在抱怨社区版中的错误！我给工作台软件打了10/10。另一方面，如果您运行的是标准版，请联系 MySQL。
（我正在运行 MySQL Workbench v5.2.34 CE，Rev 7780。）
干杯
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
这听起来像是你的设计工具的问题，我不熟悉。
外键约束在 SQL 中始终是可选的。也就是说，外键不需要引用表中的一行，它只要求每个引用行在被引用的表（父表）中都有对应的行。这是 ER 建模不能很好地映射到 SQL 数据库的一种方式。您可以在 ER 图中绘制强制 1 到 N (N>0) 关系的图片，但对于大多数实际用途，您无法在 SQL 中实现 - 它实际上始终是 1 到 0/N。
尽管允许可以为空的外键，但我不建议您这样做。外键中的空值可能会导致以后出现错误结果的问题，它们在不同的 DBMS 中的工作方式不同，并且即使是专家用户也往往无法正确理解。
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.