MySQL MySQL外键允许NULL吗？

MySQL foreign key to allow NULL?

提问by kyle

I'm piecing together an image website. The basic schema's pretty simple MySQL, but I'm having some trouble trying to represent possible admin flags associated with an image ("inappropriate", "copyrighted", etc.). My current notion is as follows:

我正在拼凑一个图片网站。基本架构是非常简单的 MySQL，但我在尝试表示与图像相关的可能管理标志（“不适当”、“受版权保护”等）时遇到了一些麻烦。我目前的想法如下：

tblImages (
    imageID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ...
);

tblImageFlags (
    imageFlagID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    imageID INT UNSIGNED NOT NULL,
    flagTypeID INT UNSIGNED NOT NULL,
    resolutionTypeID INT UNSIGNED NOT NULL,
    ...
);

luResolutionTypes (
    resolutionTypeID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    resolutionType VARCHAR(63) NOT NULL,
    ...
);

(truncated for ease of reading; assorted foreign keys and indexes are in order, I swear)

（为了便于阅读而被截断；我发誓，各种外键和索引是有序的）

tblImageFlags.flagTypeIDis foreign-keyed on a lookup table of flag types, and as you can imagine tblImageFlags.resolutionTypeIDshouldbe foreign-keyed on luResolutionTypes.resolutionTypeID. The issue at hand is that, when a flag is first issued, there is no logical resolution type (I'd declare this a good use of NULL); however, if a value is set, it should be foreign-keyed to the lookup table.

tblImageFlags.flagTypeID在标志类型的查找表上是外键的，你可以想象tblImageFlags.resolutionTypeID应该是外键的luResolutionTypes.resolutionTypeID。手头的问题是，当第一次发出标志时，没有逻辑解析类型（我声明这是一个很好的用法NULL）；但是，如果设置了一个值，它应该是查找表的外键。

I can't find a MySQL syntax workaround to this situation. Does it exist? The best runners up are:

我找不到针对这种情况的 MySQL 语法解决方法。它存在吗？最好的亚军是：

• Add an "unmoderated" resolution type
• Add a NULLentry to luResolutionTypes.resolutionTypeID(would this even work in an AUTO_INCREMENTcolumn?)

• 添加“未经审核”的分辨率类型
• 添加一个NULL条目luResolutionTypes.resolutionTypeID（这甚至可以在AUTO_INCREMENT列中工作吗？）

Thanks for the insight!

感谢您的洞察力！

PS Bonus points to whomever tells me whether, in the case of databases, it's "indexes" or "indices".

PS Bonus 指向任何人告诉我，在数据库的情况下，它是“索引”还是“索引”。

---

---

Follow-up: thanks to Bill Karwin for pointing out what turned out to be a syntax error in the table structure (don't set a column to NOT NULLif you want it to allow NULL!). And once I have enough karma to give you those bonus points, I will :)

跟进：感谢 Bill Karwin 指出表结构中的语法错误（NOT NULL如果您希望它允许，请不要将列设置为NULL！）。一旦我有足够的业力给你那些奖励积分，我会:)

回答by Bill Karwin

You can solve this by allowing NULLin the foreign key column tblImageFlags.resolutionTypeID.

您可以通过允许NULL外键列来解决此问题tblImageFlags.resolutionTypeID。

---

---

PS Bonus points to whomever tells me whether, in the case of databases, it's "indexes" or "indices".

PS Bonus 指向任何人告诉我，在数据库的情况下，它是“索引”还是“索引”。

The plural of indexshould be indexes.

index的复数应该是index。

According to "Modern American Usage" by Bryan A. Garner:

根据Bryan A. Garner 的“现代美国用法”：

For ordinary purposes, indexesis the preferable plural, not indices. ... Indices, though less pretentious than foraor dogmata, is pretentious nevertheless. Some writers prefer indicesin technical contexts, as in mathematics and the sciences. Though not the best plural for index, indicesis permissible in the sense "indicators." ... Avoid the singular indice, a back-formation from the plural indices.

对于普通用途，索引是更可取的复数形式，而不是索引。... Indices虽然不像fora或dogmata 那样自命不凡，但仍然自命不凡。一些作者更喜欢技术上下文中的索引，如数学和科学。虽然不是最好的多个指数，指数是在这个意义上允许的“指标”。...避免奇异指数之，从多个背形成指数。