Тип данных Postgres ENUM или CHECK CONSTRAINT?

Ответ 1

Основываясь на комментариях и ответах здесь, а также на некоторых элементарных исследованиях, я могу предложить следующее резюме для комментариев Postgres-erati. Поистине оцените ваш вклад.

Существует три способа ограничения записей в столбце таблицы базы данных Postgres. Рассмотрим таблицу для хранения "цветов", где вы хотите, чтобы в качестве допустимых записей были только "красные", "зеленые" или "синие".

• Перечислимый тип данных

  CREATE TYPE valid_colors AS ENUM ('red', 'green', 'blue');
  
  CREATE TABLE t (
      color VALID_COLORS
  );
  

  Преимущества заключаются в том, что тип можно определить один раз, а затем повторно использовать столько таблиц, сколько необходимо. Стандартный запрос может отображать все значения для типа ENUM и может использоваться для создания виджетов формы приложения.

  SELECT  n.nspname AS enum_schema,  
          t.typname AS enum_name,  
          e.enumlabel AS enum_value
  FROM    pg_type t JOIN 
          pg_enum e ON t.oid = e.enumtypid JOIN 
          pg_catalog.pg_namespace n ON n.oid = t.typnamespace
  WHERE   t.typname = 'valid_colors'
  
   enum_schema | enum_name     | enum_value 
  -------------+---------------+------------
   public      | valid_colors  | red
   public      | valid_colors  | green
   public      | valid_colors  | blue
  

  Недостатки: тип ENUM хранится в системных каталогах, поэтому для просмотра его определения требуется запрос, как указано выше. Эти значения не очевидны при просмотре определения таблицы. И поскольку тип ENUM на самом деле является типом данных, отдельно от встроенных типов данных NUMERIC и TEXT, обычные числовые и строковые операторы и функции на нем не работают. Таким образом, невозможно выполнить запрос типа

  SELECT FROM t WHERE color LIKE 'bl%'; 
  

• Проверить ограничения

  CREATE TABLE t (
      colors TEXT CHECK (colors IN ('red', 'green', 'blue'))
  );
  

  Два преимущества: одно: "то, что вы видите, это то, что вы получаете", то есть допустимые значения для столбца записываются прямо в определении таблицы, а два - все собственные строковые или числовые операторы работают.

• Внешние ключи

  CREATE TABLE valid_colors (
      id SERIAL PRIMARY KEY NOT NULL,
      color TEXT
  );
  
  INSERT INTO valid_colors (color) VALUES 
      ('red'),
      ('green'),
      ('blue');
  
  CREATE TABLE t (
      color_id INTEGER REFERENCES valid_colors (id)
  );
  

  По существу то же самое, что и создание типа ENUM, за исключением того, что работают родные числовые или строковые операторы, и нет необходимости запрашивать системные каталоги для обнаружения действительных значений. Для привязки color_id к требуемому текстовому значению требуется соединение.

Ответ 2

PostgreSQL имеет перечислять типы, работает как следует. Я не знаю, является ли переименование "лучше", чем ограничение, они оба работают.

Ответ 3

Как указывают другие ответы, ограничения проверки имеют проблемы с гибкостью, но установка внешнего ключа на идентификатор целого требует соединения во время поиска. Почему бы просто не использовать допустимые значения как естественные ключи в справочной таблице?

Чтобы адаптировать схему из punkish answer:

CREATE TABLE valid_colors (
    color TEXT PRIMARY KEY
);

INSERT INTO valid_colors (color) VALUES 
    ('red'),
    ('green'),
    ('blue');

CREATE TABLE t (
    color TEXT REFERENCES valid_colors (color) ON UPDATE CASCADE
);

Значения хранятся в строке, как и в случае ограничения проверки, поэтому нет объединений, но новые значения допустимого значения могут быть легко добавлены, а экземпляры существующих значений могут быть обновлены с помощью ON UPDATE CASCADE (например, если он решил, что "красный" должен фактически "Красный", обновите valid_colors соответственно, и изменение будет автоматически распространяться).

Ответ 4

Один из больших недостатков внешних ключей и ограничений проверки заключается в том, что для отображения отчетов или отображения пользовательского интерфейса необходимо выполнить соединение, чтобы разрешить идентификатор для текста.

В небольшой системе это не имеет большого значения, но если вы работаете над HR или подобной системой с очень большим количеством небольших таблиц поиска, тогда это может быть очень большой проблемой, так как много соединений происходит только для получения текста.

Моя рекомендация заключалась бы в том, что если значения мало и редко меняются, тогда используйте ограничение в текстовом поле, иначе используйте таблицу поиска с целым полем id.

Ответ 5

Я надеюсь, что кто-то будет отвечать хорошим ответом из базы данных PostgreSQL относительно того, почему кто-то может быть предпочтительнее другого.

С точки зрения разработчика программного обеспечения у меня есть небольшое предпочтение использовать контрольные ограничения, поскольку для перечисления PostgreSQL требуется, чтобы в вашем SQL выполнялось обновление/вставка, например:

INSERT INTO table1 (colA, colB) VALUES('foo', 'bar'::myenum)

где "myenum" - это тип перечисления, указанный вами в PostgreSQL.

Это, безусловно, делает SQL не переносным (что не может быть большой проблемой для большинства людей), но и это еще одна вещь, с которой вам приходится иметь дело при разработке приложений, поэтому я предпочитаю иметь VARCHAR (или другие типичные примитивы ) с контрольными ограничениями.

Как замечание, я заметил, что перечисления MySQL не требуют такого типа приведения, так что это то, что характерно для PostgreSQL в моем опыте.