Context
Contact information table In a contact information table one field is restricted to some values. But your company is opening a new office in France. You need to be able to support M., Mme., Mlle values. You need to alter the table which is not ideal. Also there are rumors about some office in Cairo.
CREATE TABLE ContactInfo (
...
salutation VARCHAR(4)
CHECK (salutation IN ('Mr.', 'Mrs.', 'Ms.'))
);
Antipattern
Specific values in the column definition Mysql supports a nonstandard data type called ENUM.
CREATE TABLE Bugs (
...
status ENUM('NEW', 'IN PROGRESS', 'FIXED')
);
Internally the column is stored as the ordinal number of the string in enumerated list.
When you sort a query by this column, the result is ordered by this number, not alphabetically.
How do you query the database for an enumerated list of values that are allowed in the status column?
There is no syntax for adding or removing to ENUM column.
Some databases can't change the definition of column unless the table is empty.
You need to extract, transform, and load again. Common enought that it has a name ETL.
If you make a value obsolete, you could upset historical data.
Portability is hard, ENUM data type is a proprietary feature in MySQL.
ENUM could be ok when you have only two exclusive values (left/right, on/off).
Solution
Create a lookup table for status
CREATE TABLE BugStatus (
status VARCHAR(20) PRIMARY KEY
);
INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');
CREATE TABLE Bugs (
...
status VARCHAR(20),
FOREIGN KEY (status) REFERENCES BugStatus(status)
ON UPDATE CASCADE
);
SELECT status FROM BugStatus ORDER by status;
INSERT INTO BugStatus (status) VALUES ('DUPLICATE');
UPDATE BugStatus SET status = 'INVALID' WHERE status = 'BOGUS';
Last update: 221 days ago