0

I am trying to insert into a table (mdata) which has a compost foreign key referencing to a primary keys in another table (measurement), unfortunately I have this error

ERROR:  insert or update on table "mdata" violates foreign key constraint "FK_mdata"
DETAIL:  Key (time, measurement_id)=(2022-07-18 12:35:03.31052, 1) is not present in table "measurement".
SQL state: 23503 

Note that the foreign key data exist surly in the reference table. below are my two tables

CREATE TABLE IF NOT EXISTS public.mdata
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    value jsonb NOT NULL,
    measurement_id bigint NOT NULL,
    "time" timestamp without time zone NOT NULL,
    CONSTRAINT "PK_medata" PRIMARY KEY (id),
    CONSTRAINT "FK_mdata" FOREIGN KEY ("time", measurement_id)
        REFERENCES public.measurement ("time", id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

and

CREATE TABLE IF NOT EXISTS public.measurement
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "time" timestamp without time zone NOT NULL,
    value real NOT NULL,
    CONSTRAINT "PK_measurement" PRIMARY KEY ("time", id),

 )

the problem is that I don't see the issue, because I am sure what I am inserting into mdata (measurement_id and "time") surly exist in the measurement table. Could someone give a hint about the problem ?

I am trying to insert like this

INSERT INTO public.mdata(
id, value, measurement_id, "time")
VALUES (8, '{}',1 , '2022-07-18 12:35:03.31052');
2
  • 1
    You are sure that the referenced values exist, but Postgres is sure they are not; who do we believe?
    – mustaccio
    Commented Jul 19, 2022 at 12:09
  • We believe both! the inheritance in table measurement was causing the INSERT VIOLATION. since I am working with pgadmin there was no documentation about small icon and delayed me from knowing the reason: stackoverflow.com/questions/68380010/…
    – Raziel
    Commented Jul 20, 2022 at 8:26

1 Answer 1

0

After investigation the only possible reason for this, it that the table measurement might be an inherited table, thanks to @Craig Ringer for this answer (https://stackoverflow.com/questions/24360312/foreign-keys-table-inheritance-in-postgresql), which pushed me to double check if there is any inheritance to my table: https://www.postgresql.org/docs/current/ddl-inherit.html#DDL-INHERIT-CAVEATS

So, indeed, the inheritance was causing the INSERT VIOLATION.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.