MySQL外键是否允许空?

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.flagTypeID is foreign-keyed on a lookup table of flag types, and as you can imagine tblImageFlags.resolutionTypeID should be 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。在luresolution .resolution .resolution . typeid上应该使用外键。目前的问题是，当第一次发出标志时，没有逻辑解析类型(我将声明这是正确使用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 NULL entry to luResolutionTypes.resolutionTypeID (would this even work in an AUTO_INCREMENT column?)
• 向luresolution类型添加空条目。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 NULL if you want it to allow NULL!). And once I have enough karma to give you those bonus points, I will :)

后续:感谢Bill Karwin指出了在表结构中出现的语法错误(如果您希望它允许NULL，不要设置列为NULL!)一旦我有足够的业力给你这些积分，我会:)

1 个解决方案

#1

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

您可以通过在外键列tblimageflag .resolution . typeid中允许NULL来解决这个问题。

---

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

PS Bonus指向那些告诉我在数据库中是“索引”还是“索引”的人。

The plural of index should be indexes.

索引的复数应该是索引。

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

根据布莱恩·a·加纳的《现代美国用法》:

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

一般而言，索引是更可取的复数，而不是索引。指数虽然不如fora或dogmata那么自命不凡，但仍然很自命不凡。有些作家喜欢在技术环境中使用指数，如数学和科学。指数虽然不是最好的复数，但在“指标”的意义上是允许的。…避免使用单数形式，也就是复数形式的反义词。