MySQL foreign key to allow NULL?

Question

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

Traynor

Answer 1

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

---

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

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.

Hoyden

What do you do with foreign keys to primary key columns which aren't allowed to be NULL? This is a problem that has plagued my apps over the years and I sometimes end up using 0, or foregoing the foreign key relation, but that is not optimal. – Saltwort

@glyph: That makes no sense. If the FK column may reference no specific PK entry, then make the FK nullable. If the FK column is not allowed to be null, then it must reference an existing entry in the PK column. Using 0 or some other fake value to indicate "no entry referenced" is not valid. – Hoyden

In this case (at least in MySQL) the foreign key won't be allowed, because the fk can't be null if the pk is not null, and pks in mysql are always not null. What I'm getting at is how to enforce referential integrity between the fk field and pk column if a value is present, but also allow null values. Using a 0 as a dummy entry is definitely a hack that requires workarounds elsewhere in your app, but it's valid. – Saltwort

@glyph: No, you can allow null in a FK, at least if you don't declare the FK column as NOT NULL. – Hoyden

Well I just did an experiment and my problem was a that my pk fields had a default value. PK field is NULL but default is none, then FK field can be NULL and everything is good. – Saltwort

Indexes is indeed more common in American English - but less so in British English where indices is used more frequently, especially in technical contexts... grammarist.com/usage/indexes-indices – Contrarious

@Basic, thanks for the insight! I read the difference as indices is used in the context of measures or indicators, whereas indexes is more appropriate when talking about a collection of organized data. – Hoyden

Great... now I need to analyze if my DB architectures sound pretencious or not. – Cathey

@CamiloMartin It's not an English lesson, instead an American lesson. It would be pretentious for an American to inform English speakers how to speak their own language! – Continent

@Continent *American English. As a non-native speaker, though: do brits use "indices"? – Dorothydorp

@ShaheenGhiassy, did you not read the part where the OP said, "PS Bonus points to whomever tells me whether, in the case of databases, it's "indexes" or "indices"." You are being too zealous. – Hoyden

@BillKarwin - I did miss that. Thx for showing me the point I missed – Tutuila

I'm usually really picky about correct grammar, but referring to indexes doesn't bother me at all. – Azobenzene

@Cathey actually it's spelled pretenscious. – Hydr

How did a Stack Overflow question turn into a language class? – Peplos

@ADTC, the OP asked for help on the usage of a programming term. I think that counts as a programming-related question. :-) – Hoyden