Values in Attribute Definition

This smell arises when specific values are defined in an attribute definition to restrict possible values of the attribute.

Specifying all possible values for an attribute in schema definition mixes metadata with data which is not recommended. This smell makes it difficult to extend or modify the list of accepted values for an attribute.

Example

CREATE TABLE Bugs (
-- other columns
status VARCHAR(20) CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED'))
);

Tools

The following set of tools detects this smell: DbDeo(for SQL)

Reference

B. Karwin, “SQL Antipatterns: Avoiding the Pitfalls of Database Programming”. Pragmatic Bookshelf, 2010.