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

问题描述

我正在拼凑一个图片网站.基本架构是非常简单的 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:

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 在标志类型的查找表上是外键的，你可以想象 tblImageFlags.resolutionTypeID should 是外键- 键入 luResolutionTypes.resolutionTypeID.手头的问题是，当第一次发出标志时，没有逻辑解析类型(我声明这是 NULL 的一个很好的用法)；但是，如果设置了一个值，它应该是查找表的外键.

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.

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

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

• 添加未经审核"的分辨率类型
• 向 luResolutionTypes.resolutionTypeID 添加一个 NULL 条目(这甚至可以在 AUTO_INCREMENT 列中使用吗?)

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

感谢您的洞察力！

PS Bonus 指向任何人告诉我，就数据库而言，它是索引"还是索引".

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

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

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

推荐答案

您可以通过在外键列 tblImageFlags.resolutionTypeID 中允许 NULL 来解决这个问题.

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

PS Bonus 指向任何人告诉我，就数据库而言，它是索引"还是索引".

index的复数应该是indexes.

根据现代美国用法"布莱恩 A. 加纳:

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

对于一般用途，indexes 是首选复数，而不是索引....索引，虽然不如fora或dogmata那么自命不凡，不过是自命不凡.一些作者更喜欢索引技术背景，如数学和科学.虽然不是最好的index 的复数形式，indices 是在指标"的意义上是允许的....避免使用单数 indice，这是复数 indices 的反形式.

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屋！