Tengo dos mesas, una llamada papeletas y otra llamada votos. Ballots almacena una lista de cadenas que representan opciones por las que las personas pueden votar:

CREATE TABLE IF NOT EXISTS Polls (
  id SERIAL PRIMARY KEY,
  options text[]
);

Votos almacena los votos que han realizado los usuarios (donde un voto es un número entero que representa el índice de la opción por la que votaron):

CREATE TABLE IF NOT EXISTS Votes (
  id SERIAL PRIMARY KEY,
  poll_id integer references Polls(id),
  value integer NOT NULL ,
  cast_by integer NOT NULL
);

Quiero asegurarme de que cada vez que se crea una fila en la tabla Votos, el valor de 'valor' está en el rango [0, longitud (opciones)) para la fila correspondiente en Encuestas (por corresponder, me refiero a la fila donde los id de encuesta) partido).

¿Existe algún tipo de control o restricción de clave externa que pueda implementar para lograr esto? ¿O necesito algún tipo de disparador? Si es así, ¿cómo se vería ese disparador y habría problemas de rendimiento? ¿Sería igual de eficaz consultar manualmente la encuesta correspondiente utilizando una declaración SELECT y luego afirmar que el 'valor' es válido antes de insertarlo en la tabla de votos?

1
user2779450 14 ago. 2020 a las 06:52

2 respuestas

La mejor respuesta

Le sugiero que modifique su modelo de datos para tener una tabla, PollOptions:

CREATE TABLE IF NOT EXISTS PollOptions (
  PollOptionsId SERIAL PRIMARY KEY,  -- should use generated always as identity
  PollId INT NOT NULL, REFERENCES Polls(id),
  OptionNumber int,
  Option text,
  UNIQUE (PollId, Option)
);

Entonces su tabla Votes debería tener una referencia de clave externa a PollOptions. Puede utilizar PollOptionId o (PollId, Option).

No se necesitan activadores ni funciones especiales si configura los datos correctamente.

1
Gordon Linoff 14 ago. 2020 a las 12:39

En su requerimiento no puede usar Check Constraint porque puede referirse a la columna de la misma tabla.

Puede consultar el Manual oficial. por lo mismo.

Entonces, aquí debe usar Trigger en el evento BEFORE INSERT de su tabla Votes o puede usar la función / procedimiento (dependiendo de su versión de PostgreSQL) para su operación de inserción donde puede verificar el valor antes de insertar y generar una excepción si la condición no se cumple.

USANDO Trigger:

create or replace function id_exist() returns trigger as
$$
begin
if new.value<0 or new.value>=(select array_length(options,1) from polls where id=new.poll_id) then
raise exception 'value is not in range';
end if;
return new;
end;

$$
language plpgsql

CREATE TRIGGER check_value BEFORE INSERT  ON votes
    FOR EACH ROW EXECUTE PROCEDURE id_exist();

Demo

1
Akhilesh Mishra 14 ago. 2020 a las 04:41