MySQL外键允许NULL？ [英] MySQL foreign key to allow NULL?

问题描述

我在拼凑图像网站。基本模式相当简单的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，
 ... 
）; 
  

（为方便阅读而截断;分发的外键和索引是顺序的， p>

tblImageFlags.flagTypeID 是外键锁定在标记类型的查找表上，您可以想象 tblImageFlags.resolutionTypeID 应在 luResolutionTypes.resolutionTypeID 上外键。现在的问题是，当一个标志首次发出时，没有逻辑分辨率类型（我会声明这是一个很好的使用 NULL ）;但是，如果一个值被设置，它应该被外键到查找表。

我找不到一个MySQL语法解决这种情况。它存在吗？最好的选手是：

• 添加未经审核的解析类型


• 添加 NULL 条目 luResolutionTypes.resolutionTypeID （这甚至可以在 AUTO_INCREMENT column？）

感谢您的了解！

---

后续：感谢Bill Karwin指出原来是表结构中的语法错误（不要将列设置为 NOT NULL 如果你想让它允许 NULL ！）。一旦我有足够的因果给你这些奖励点，我会：）

解决方案

c $ c> NULL 在外键列 tblImageFlags.resolutionTypeID 。

。

根据 Modern American Usage ：Bryan A. Garner：

对于一般用途，索引是
的优选复数，而不是 indices 。
...
指数，尽管不如 或
一些作者喜欢 b 在技术上下文中，如数学
和科学。虽然对于索引不是最好的
复数，但 是
在指标的意义上允许的。
...
避免使用单数 indice ，这是多个索引的反义。

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:

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.

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

• Add an "unmoderated" resolution type
• Add a NULL entry to luResolutionTypes.resolutionTypeID (would this even work in an AUTO_INCREMENT column?)

Thanks for the insight!

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

---

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 :)

解决方案

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

The plural of index should be indexes.

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

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.

这篇关于MySQL外键允许NULL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！