Databases
StackOverflow2013
Postsdbo
POMySQL foreign key to allow NULL?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL foreign key to allow NULL?
    primarykey
    data
    text
    <p>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:</p> <pre><code>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, ... ); </code></pre> <p>(truncated for ease of reading; assorted foreign keys and indexes are in order, I swear)</p> <p><code>tblImageFlags.flagTypeID</code> is foreign-keyed on a lookup table of flag types, and as you can imagine <code>tblImageFlags.resolutionTypeID</code> <em>should</em> be foreign-keyed on <code>luResolutionTypes.resolutionTypeID</code>. 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 <code>NULL</code>); however, if a value is set, it should be foreign-keyed to the lookup table.</p> <p>I can't find a MySQL syntax workaround to this situation. Does it exist? The best runners up are:</p> <ul> <li>Add an "unmoderated" resolution type</li> <li>Add a <code>NULL</code> entry to <code>luResolutionTypes.resolutionTypeID</code> (would this even work in an <code>AUTO_INCREMENT</code> column?)</li> </ul> <p>Thanks for the insight!</p> <p>PS Bonus points to whomever tells me whether, in the case of databases, it's "indexes" or "indices".</p> <hr> <p><strong>Follow-up</strong>: 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 <code>NOT NULL</code> if you want it to allow <code>NULL</code>!). And once I have enough karma to give you those bonus points, I will :)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCamilo Martin
    1. USkyle
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL foreign key to allow NULL?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL foreign key to allow NULL?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL foreign key to allow NULL?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload