Hi all.
I have a cuious problem. First look at my tables:
-----------------------------------------------------
create table aviso (
número serial primary key,
fecha date default CURRENT_DATE,
hora time default CURRENT_TIME,
usuario int4 references empleado(cod),
procedencia int4 references procedencia,
línea int4 references línea,
empresa int4 references empresa,
urgente bool default 'f',
estado int4 references estado,
fecha_terminado date,
orden int4,
detalle text
);
create index avi_fecha_ndx on aviso (fecha);
create index avi_hora_ndx on aviso (hora);
create index avi_usuario_ndx on aviso (usuario);
create index avi_procedencia_ndx on aviso (procedencia);
create index avi_linea_ndx on aviso (línea);
create index avi_empresa_ndx on aviso (empresa);
create index avi_urgente_ndx on aviso (urgente);
create unique index avi_orden_ndx on aviso (orden);
create index avi_estado_ndx on aviso (estado);
create index avi_fecha_terminado_ndx on aviso (fecha_terminado);
---------------------------------
create table transferencia (
aviso int4,
técnico int4,
fecha date default current_date,
hora time default current_time,
importe float,
concepto int4,
a_caja boolean default false
);
---------------------------------
create table gasto (
cod serial primary key,
foreign key (técnico) references empleado(cod),
foreign key (aviso) references aviso(número),
foreign key (concepto) references concepto_gasto(cod)
) inherits (transferencia);
create index gasto_aviso_ndx on gasto (aviso);
create index gasto_tecnico_ndx on gasto (técnico);
create index gasto_concepto_ndx on gasto (concepto);
create index gasto_fecha_ndx on gasto (fecha);
create index gasto_hora_ndx on gasto (hora);
create function gasto_cambiar_caja() returns opaque as '
declare
begin
if NEW.a_caja and NEW.importe is not null then
insert into movimiento_caja(importe,fecha,empleado) values (
- NEW.importe,
NEW.fecha,
get_usuario());
end if;
return null;
end;
' language 'plpgsql';
create trigger gasto_cambiar_caja after insert on gasto
for each row execute procedure gasto_cambiar_caja();
-----------------------------------------------------------
Database has more tables (obviously), but I supose it is enought with
these.
When I try next query:
# update gasto SET importe = round(importe,2);
I get error:
ERROR: <unnamed> referential integrity violation - key
referenced from gasto not found in aviso
I supose this error means that there is a reference in field "aviso"
of table "gasto" to inexistent "número" from "aviso" (data was
imported with copy from other database).
If it is so, then inserting data again in table "gasto" with inserts
instead of copy sould fix situation, revoking inserts that woldn't
comply with referential integrity rules.
I have do it so:
pg_dump -a -d -f foo.sql -t gasto database
and after truncate data:
psql database -f foo.sql
Then, when I try my query again, I get same error. Have you any idea
of actual problem? Where am I wrong in my asumptions?.
Thanks in advance.
David
