SERIAL personalizado/incremento automático por grupo de valores
Estoy intentando crear una especie de sistema de blogs y me encontré con un pequeño problema.
En pocas palabras, hay 3 columnas en mi articletabla:
id SERIAL,
category VARCHAR FK,
category_id INT
idLa columna es obviamente la PK y se utiliza como identificador global para todos los artículos.
categoryLa columna está bien ... categoría.
category_idse utiliza como UNIQUEID dentro de una categoría, por lo que actualmente existe una UNIQUE(category, category_id)restricción.
Sin embargo, también quiero que category_idse incremente automáticamente .
Lo quiero para que cada vez que ejecute una consulta como
INSERT INTO article(category) VALUES ('stackoverflow');
Quiero que la category_idcolumna se complete automáticamente de acuerdo con lo último category_idde la categoría 'stackoverflow'.
Lograr esto en mi código lógico es bastante fácil. Simplemente selecciono el último número e inserto +1 de eso, pero eso implica dos consultas separadas. Estoy buscando una solución SQL que pueda hacer todo esto en una sola consulta.
Esto se ha preguntado muchas veces y la idea general está destinada a fallar en un entorno multiusuario , y un sistema de blog parece exactamente ese caso.
Entonces la mejor respuesta es: no lo hagas. Considere un enfoque diferente.
Elimine la columna category_id(id, category)no almacenarían ya.
Su ides una serialcolumna y ya se incrementa automáticamente de manera confiable.
- Función SQL de incremento automático
Si necesita algún tipo de category_idsin espacios category, generelo sobre la marcha con row_number():
- Números de serie por grupo de filas para clave compuesta
Concepto
Hay al menos varias maneras de abordar esto. El primero que me viene a la mente:
Asigne un valor a category_idla columna dentro de un activador ejecutado para cada fila, sobrescribiendo el valor de entrada de INSERTla declaración.
Acción
Aquí está el SQL Fiddlepara ver el código en acción.
Para una prueba sencilla, estoy creando articletablas con categorías y sus idcategorías que deberían ser únicas para cada una category. He omitido la creación de restricciones; eso no es relevante para presentar el punto.
create table article ( id serial, category varchar, category_id int )
Insertar algunos valores para dos categorías distintas usando generate_series()la función para tener un incremento automático ya implementado.
insert into article(category, category_id)
select 'stackoverflow', i from generate_series(1,1) i
union all
select 'stackexchange', i from generate_series(1,3) i
Crear una función de activación, que seleccionaría MAX(category_id)e incrementaría su valor en 1el caso en el que categoryestamos insertando una fila y luego sobrescribiría el valor justo antes de pasar a la INSERTtabla real ( BEFORE INSERTel activador se encarga de eso).
CREATE OR REPLACE FUNCTION category_increment()
RETURNS trigger
LANGUAGE plpgsql
AS
$$
DECLARE
v_category_inc int := 0;
BEGIN
SELECT MAX(category_id) + 1 INTO v_category_inc FROM article WHERE category = NEW.category;
IF v_category_inc is null THEN
NEW.category_id := 1;
ELSE
NEW.category_id := v_category_inc;
END IF;
RETURN NEW;
END;
$$
Usando la función como disparador.
CREATE TRIGGER trg_category_increment
BEFORE INSERT ON article
FOR EACH ROW EXECUTE PROCEDURE category_increment()
Insertar algunos valores más (dispositivo de activación posterior) para categorías ya existentes y no existentes.
INSERT INTO article(category) VALUES
('stackoverflow'),
('stackexchange'),
('nonexisting');
Consulta utilizada para seleccionar datos:
select category, category_id From article order by 1,2
Resultado de las inserciones iniciales:
category category_id
stackexchange 1
stackexchange 2
stackexchange 3
stackoverflow 1
Resultado después de las inserciones finales:
category category_id
nonexisting 1
stackexchange 1
stackexchange 2
stackexchange 3
stackexchange 4
stackoverflow 1
stackoverflow 2
