This query identifies base table columns designated for storing codes (e.g., vin and isbn codes) that lack appropriate length constraints reflecting real-world data requirements. It operates on a heuristic basis, targeting columns whose identifiers imply code data (e.g., names containing "isbn" or "vin") but whose definitions fail to account for standard maximum lengths. This includes both insufficient allocation (truncation risk) and unbounded allocation (data quality risk). Ensuring these fields are sized according to domain standards is crucial for data integrity and usability.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH codes AS (SELECT table_schema, table_name, column_name, data_type, character_maximum_length,
CASE WHEN column_name~*'(vin.*(code|kood))' AND character_maximum_length<>17 THEN 'A standard Vehicle Identification Number (VIN) has a maximum and fixed length of 17 characters.'
WHEN column_name~*'(isbn)' AND character_maximum_length NOT IN (13,17) THEN 'The maximum length of an ISBN (International Standard Book Number) is 13 digits. A standard 13-digit ISBN (ISBN-13) has four hyphens, which divide the number into five distinct parts. Old ISBNs (known as ISBN-10) are exactly 10 digits long. A 10-digit ISBN (ISBN-10) typically contains three hyphens when properly formatted'
ELSE 'other' END AS mistake
FROM INFORMATION_SCHEMA.columns
WHERE data_type~*'char'
AND column_name~*'((vin.*(code|kood))|isbn)')
SELECT table_schema, table_name, column_name, data_type, character_maximum_length, mistake
FROM codes
WHERE mistake<>'other'
ORDER BY mistake, character_maximum_length, table_schema, table_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
Field size
Queries of this category provide information about the maximum size of values that can be recorded in column fields
Result quality depends on names
Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.