Postgres ENUM data type or CHECK CONSTRAINT?

Question

Postgres ENUM data type or CHECK CONSTRAINT?

He estado migrando una base de datos MySQL a Pg (9.1), y he estado emulando tipos de datos MySQL ENUM creando un nuevo tipo de datos en Pg, y luego usándolo como definición de columna. Mi pregunta could ¿podría, y sería mejor, usar una RESTRICCIÓN de VERIFICACIÓN en su lugar? Los tipos de enumeración de MySQL se implementan para imponer entradas de valores específicos en las filas. ¿Se podría hacer con una RESTRICCIÓN de VERIFICACIÓN? y, si es así, ¿sería mejor (o peor)?

36

postgresql postgresql-9.1

Author: Paulo Freitas, 2012-06-07

Source

5 answers

Como señalan otras respuestas, las restricciones de verificación tienen problemas de flexibilidad, pero establecer una clave foránea en un id 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 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 a través de 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).

7

Author: Gord Stephen,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:54:16

PostgreSQL tiene tipos de enum, funciona como debería. No se si una enumeración es "mejor" que una restricción, simplemente ambas funcionan.

3

Author: Frank Heikens,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-06-08 05:01:28

Una de las grandes desventajas de las claves foráneas frente a las restricciones de Comprobación es que cualquier presentación de informes o interfaz de usuario tendrá que realizar una combinación para resolver el id del 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 con muchas uniones que tienen lugar solo para obtener el texto.

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

1

Author: tomo7,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-05-23 18:47:40

Espero que alguien pueda dar una buena respuesta del lado de la base de datos PostgreSQL sobre por qué uno podría ser preferible al otro.

Desde el punto de vista de un desarrollador de software, tengo una ligera preferencia por el uso de restricciones de verificación, ya que las enumeraciones de PostgreSQL requieren un cast en su SQL para hacer una actualización / inserción, como:

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

Donde "myenum" es el tipo de enumeración que especificó en PostgreSQL.

Esto ciertamente hace que el SQL no sea portable (lo que puede no ser un gran problema para la mayoría de la gente), pero también es solo otra cosa con la que tiene que lidiar mientras desarrolla aplicaciones, por lo que prefiero tener VARCHARs (u otras primitivas típicas) con restricciones de verificación.

Como nota al margen, he notado que las enum MySQL no requieren este tipo de cast, por lo que esto es algo particular para PostgreSQL en mi experiencia.

0

Author: quux00,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-06-09 21:45:44

score 58 · Accepted Answer

Basado en los comentarios y respuestas aquí, y algunas investigaciones rudimentarias, tengo el siguiente resumen para ofrecer para los comentarios de los Postgres-erati. Realmente apreciará sus comentarios.

Hay tres maneras de restringir entradas en una columna de tabla de base de datos Postgres. Considere una tabla para almacenar "colores" donde desea que solo' rojo',' verde 'o' azul ' sean entradas válidas.

Tipo de datos enumerado
```
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 reutilizado en tantas tablas como sea necesario. Una consulta estándar puede listar todos los valores de un tipo de enumeración, y se puede usar para crear widgets de formulario 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 de ENUMERACIÓN se almacena en los 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 de ENUMERACIÓN es en realidad un tipo de datos separado de los tipos de datos NUMÉRICOS y de TEXTO incorporados, los operadores numéricos y de cadena regulares y las funciones no funcionan en él. Por lo tanto, uno no puede hacer una consulta como
```
SELECT FROM t WHERE color LIKE 'bl%'; 
```
Comprobar restricciones
```
CREATE TABLE t (
    colors TEXT CHECK (colors IN ('red', 'green', 'blue'))
);
```
Dos ventajas son que, uno, "lo que ves es lo que obtienes", es decir, los valores válidos para la columna se registran directamente en la definición de la tabla, y dos, todos los operadores de cadena o numéricos nativos funcionan.
Claves foráneas
```
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 de enumeración, excepto que los operadores numéricos o de cadena nativos funcionan, y uno no tiene que consultar los catálogos del sistema para descubrir los valores válidos. Se requiere una combinación para vincular color_id al valor de texto deseado.