sql - partir - SERIE personalizado/autoincrement por grupo de valores

insertar registro auto_increment sql

Question

Estoy tratando de hacer un sistema de blog de tipo y me encontré con un pequeño problema.

En pocas palabras, hay 3 columnas en mi tabla de article :

id SERIAL,
category VARCHAR FK,
category_id INT

id columna id es obviamente la PK y se usa como un identificador global para todos los artículos.

category columna de category está bien .. categoría.

category_id se usa como una ID UNIQUE dentro de una categoría, por lo que actualmente existe una restricción UNIQUE(category, category_id) establecida.

Sin embargo, también quiero que category_id aumente automáticamente .

Lo quiero para que cada vez que ejecuto una consulta como

INSERT INTO article(category) VALUES (''stackoverflow'');

Quiero que la columna category_id se llene automáticamente de acuerdo con el último category_id de la category_id ''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 involucra dos consultas separadas. Estoy buscando una solución SQL que pueda hacer todo esto en una consulta.

Answer 1

Concepto

Hay al menos varias formas de abordar esto. El primero que viene a mi mente:

Asigne un valor para la columna category_id dentro de un disparador ejecutado para cada fila, sobrescribiendo el valor de entrada de la INSERT .

Acción

Aquí está el SQL Fiddle para ver el código en acción

Para una prueba simple, estoy creando tablas de article con categorías y sus id que deberían ser únicas para cada 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 utilizando la función generate_series() para tener un auto-incremento ya en su lugar.

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

Creando una función de disparo, que seleccionaría MAX(category_id) e incrementaría su valor por 1 para una category que estamos insertando una fila y luego sobrescribirá el valor justo antes de continuar con el INSERT real a la tabla ( BEFORE INSERT trigger se ocupe de ese).

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 un 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 para inserciones iniciales :

category    category_id
stackexchange   1
stackexchange   2
stackexchange   3
stackoverflow   1

Resultado después de insertos finales:

category    category_id
nonexisting     1
stackexchange   1
stackexchange   2
stackexchange   3
stackexchange   4
stackoverflow   1
stackoverflow   2

Answer 2

Esto se ha preguntado muchas veces y es probable que la idea general falle en un entorno multiusuario , y un sistema de blog suena exactamente como tal.

Entonces la mejor respuesta es: no. Considera un enfoque diferente.

Elimine completamente la columna category_id de su tabla; no almacena ninguna información que las otras dos columnas (id, category) ya no almacenen.

Su id es una columna en serial y ya se autoevaluó de manera confiable.

• Aumento automático de la función de SQL

Si necesita algún tipo de category_id sin espacios por category , genere sobre la marcha con row_number() :

• Números de serie por grupo de filas para clave compuesta

Answer 3

Postgresql usa secuencias para lograr esto; es un enfoque diferente de lo que está acostumbrado en MySQL. Eche un vistazo a http://www.postgresql.org/docs/current/static/sql-createsequence.html para una referencia completa.

Básicamente, usted crea una secuencia (un objeto de base de datos) de la siguiente manera:

CREATE SEQUENCE serials;

Y luego, cuando quieras agregar a tu mesa, tendrás:

INSERT INTO mytable (name, id) VALUES (''The Name'', NEXTVAL(''serials'')