¿Tipo de datos ENUM de Postgres o COMPROBAR RESTRICCIÓN?

Solución:

Basado en los comentarios y respuestas aquí, y algunas investigaciones rudimentarias, tengo el siguiente resumen para ofrecer para comentarios de Postgres-erati. Realmente agradeceré tu aporte.

Hay tres formas de restringir las entradas en una columna de la tabla de la base de datos de Postgres. Considere una tabla para almacenar “colores” donde desee que solo “rojo”, “verde” o “azul” sean entradas válidas.

1. Tipo de datos enumerados

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

   Las ventajas son que el tipo se puede definir una vez y luego reutilizar en tantas tablas como sea necesario. Una consulta estándar puede enumerar todos los valores de un tipo ENUM y se puede utilizar para crear widgets de formularios de solicitud.

   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
   

   Las desventajas son que el tipo ENUM se almacena en catálogos del sistema, por lo que se requiere una consulta como la anterior para ver su definición. Estos valores no son evidentes al ver la definición de la tabla. Y, dado que un tipo ENUM es en realidad un tipo de datos separado de los tipos de datos NUMERIC y TEXT integrados, los operadores y funciones numéricos y de cadena regulares no funcionan en él. Entonces, no se puede hacer una consulta como

   SELECT FROM t WHERE color LIKE 'bl%'; 
   

2. Verificar restricciones

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

   Dos ventajas son que, una, “lo que ve es lo que obtiene”, es decir, los valores válidos para la columna se registran directamente en la definición de la tabla, y dos, todos los operadores nativos de cadena o numéricos funcionan.

3. Llaves extranjeras

   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)
   );
   

   Esencialmente lo mismo que crear un tipo ENUM, excepto que los operadores nativos numéricos o de cadena funcionan y no es necesario consultar los catálogos del sistema para descubrir los valores válidos. Se requiere una unión para vincular el color_id al valor de texto deseado.

Como señalan otras respuestas, las restricciones de verificación tienen problemas de flexibilidad, pero establecer una clave externa en un ID de número entero requiere unirse durante las búsquedas. ¿Por qué no usar los valores permitidos como claves naturales en la tabla de referencia?

Para adaptar el esquema de la respuesta de punkish:

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
);

Los valores se almacenan en línea como en el caso de restricción de verificación, por lo que no hay uniones, pero se pueden agregar fácilmente nuevas opciones de valor válido y las instancias de valores existentes se pueden actualizar mediante ON UPDATE CASCADE (por ejemplo, si se decide que “rojo” debería ser “Rojo”, actualice valid_colors en consecuencia y el cambio se propaga automáticamente).

Una de las grandes desventajas de las claves externas frente a las restricciones de verificación es que cualquier presentación de informes o interfaz de usuario tendrá que realizar una combinación para resolver la identificación en el texto.

En un sistema pequeño, esto no es un gran problema, pero si está trabajando en un sistema de recursos humanos o similar con muchas tablas de búsqueda pequeñas, esto puede ser un gran problema, ya que se realizan muchas uniones solo para obtener el texto.

Mi recomendación sería que si los valores son pocos y rara vez cambian, use una restricción en un campo de texto; de lo contrario, use una tabla de búsqueda contra un campo de identificación de número entero.

---

---

Utiliza Nuestro Buscador