MySQL foreign key to allow NULL?

Question

Hi there,

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

Answer 1

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.