MySQL Cookbook, 2nd Edition

Using Table Metadata to Validate Data

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 row in a customers table, or state abbreviations in addresses can be verified against a table that lists each state. This recipe describes ENUM- and SET-based validation, and Using a Lookup Table to Validate Data 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 in INFORMATION_SCHEMA, and 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>SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'profile'
    -> AND COLUMN_NAME = 'color'; +----------------------------------------------------+ | COLUMN_TYPE ...