SQL: Advantages of an ENUM vs. a one-to-many relationship?

I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this:

CREATE TABLE officer_ranks (
id int PRIMARY KEY
,title varchar NOT NULL UNIQUE);
INSERT INTO officer_ranks VALUES (1,'2LT'),(2,'1LT'),(3,'CPT'),(4,'MAJ'),(5,'LTC'),(6,'COL'),(7,'BG'),(8,'MG'),(9,'LTG'),(10,'GEN');

CREATE TABLE officers (
solider_name varchar NOT NULL
,rank int NOT NULL REFERENCES officer_ranks(id) ON DELETE RESTRICT
,serial_num varchar PRIMARY KEY);

But the same thing can also be shown using a user-defined type / ENUM:

CREATE TYPE officer_rank AS ENUM ('2LT', '1LT','CPT','MAJ','LTC','COL','BG','MG','LTG','GEN');
    
CREATE TABLE officers (
solider_name varchar NOT NULL
,rank officer_rank NOT NULL
,serial_num varchar PRIMARY KEY);

(Example shown using PostgreSQL, but other RDBMS’s have similar syntax)

The biggest disadvantage I see to using an ENUM is that it’s more difficult to update from within an application. And it might also confuse an inexperienced developer who’s used to using a SQL DB simply as a bit bucket.

Assuming that the information is mostly static (weekday names, month names, US Army ranks, etc) is there any advantage to using a ENUM?

Answer

A disadvantage of using something like an ENUM is that you can’t get a list of all the available values if they don’t happen to exist in your data table, unless you hard-code the list of available values somewhere. For example, if in your OFFICERS table you don’t happen to have an MG on post there’s no way to know the rank exists. Thus, when BG Blowhard is relieved by MG Marjorie-Banks you’ll have no way to enter the new officer’s rank – which is a shame, as he is the very model of a modern Major General. 🙂 And what happens when a General of the Army (five-star general) shows up?

For simple types which will not change I’ve used domains successfully. For example, in one of my databases I’ve got a yes_no_domain defined as follows:

CREATE DOMAIN yes_no_dom
  AS character(1)
  DEFAULT 'N'::bpchar
  NOT NULL
   CONSTRAINT yes_no_dom_check
     CHECK ((VALUE = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])));

Share and enjoy.