MySQL Cookbook

Validation Using Table Metadata

Problem

You need to check input values against the legal members of an ENUM or SET column.

Solution

Get the column definition, extract the list of members from it, and check data values against the list.

Discussion

Some forms of validation involve checking input values against information stored in a database. This includes values to be stored in an ENUM or SET column, which can be checked against the valid members stored in the column definition. Database-backed validation also applies when you have values that must match those listed in a lookup table to be considered legal. For example, input records that contain customer IDs can be required to match a record in a customers table, or state abbreviations in addresses can be verified against a table that lists each state. This section describes ENUM- and SET-based validation, and Recipe 10.29 discusses how to use lookup tables.

One way to check input values that correspond to the legal values of ENUM or SET columns is to get the list of legal column values into an array using the information returned by SHOW COLUMNS, then perform an array membership test. For example, the favorite-color column color from the profile table is an ENUM that is defined as follows:

mysql> SHOW COLUMNS FROM profile LIKE 'color'\G
*************************** 1. row ***************************
  Field: color
   Type: enum('blue','red','green','brown','black','white')
   Null: YES
    Key:
Default: NULL
  Extra:

If you extract the list of ...