Referential integrity violation - Mailing list pgsql-general

 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