-1

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');
philipxy
  • 14,867
  • 6
  • 39
  • 83
Raziel
  • 444
  • 6
  • 22
  • The value you inserted into `measurement` could be `2022-07-18 12:35:03.310521` rather than `2022-07-18 12:35:03.31052`. You might want to consider rounding the values to e.d. seconds using `timestamp(0)` to avoid problems with microseconds. –  Jul 19 '22 at 10:55
  • Thanks for the suggestion. Actually, this was the first thing that I checked, `2022-07-18 12:35:03.31052` really exist in the database, I even tried with different time values after inserting them into measurement like `2022-07-18 00:00:00`, unfortunately the problem persist – Raziel Jul 19 '22 at 11:03
  • Please put answers in answer post (which you did) & not in question posts. – philipxy Jul 21 '22 at 02:25

1 Answers1

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 (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.

Raziel
  • 444
  • 6
  • 22