PostgreSQL foreign key violation

Question

PostgreSQL foreign key violation

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

Good hope you are very well, I come to you for help, you will see, I have several tables in my BD but the ones I think give me problems are the tables called "project" and "winner" , the "winner" table must have the primary key of "project" as a foreign key, and in fact it has it, but moment of wanting to insert records within my table called "triunfador" I can not since I get an error that does not let me continue, I hope you can help me understand this message and explain how to solve it, I inquired in San Google but I did not find an accurate answer, here is the error:

Uncaught PDOException: SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "triunfador" violates foreign key constraint "triunfador_id_pro_fkey" DETAIL: Key (id_pro)=(6) is not present in table "proyecto". in /var/www/html/Modulos/Fermary/modelo/anadir-triunfador-modelo.php:151 Stack trace: #0 /var/www/html/Modulos/Fermary/modelo/anadir-triunfador-modelo.php(151): PDOStatement->execute() #1 /var/www/html/Modulos/Fermary/modelo/anadir-triunfador-modelo.php(131): IncluirTriunfador->IncluirTriunfador() #2 /var/www/html/Modulos/Fermary/controlador/anadir-triunfador-controlador.php(37): IncluirTriunfador->IncluirT() #3 {main} thrown in /var/www/html/Modulos/Fermary/modelo/anadir-triunfador-modelo.php on line 151

This way my "triumphant" table was created, I will place it as it appears in the pgadmin3:

 -- Table: public.triunfador

-- DROP TABLE public.triunfador;

CREATE TABLE public.triunfador
(
  id_triun integer NOT NULL DEFAULT nextval('triunfador_id_triun_seq'::regclass),
  nombre_triun character varying(50) NOT NULL,
  apellido_triun character varying(50) NOT NULL,
  cedula_triun integer NOT NULL,
  trayecto_triun integer NOT NULL,
  seccion_triun character varying(6) NOT NULL,
  condicion_triun character varying(10) NOT NULL,
  id_pro integer NOT NULL DEFAULT nextval('triunfador_id_pro_seq'::regclass),
  CONSTRAINT triunfador_pkey PRIMARY KEY (id_triun),
  CONSTRAINT triunfador_id_pro_fkey FOREIGN KEY (id_pro)
      REFERENCES public.proyecto (id_pro) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT triunfador_cedula_triun_key UNIQUE (cedula_triun)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.triunfador
  OWNER TO framework;
GRANT ALL ON TABLE public.triunfador TO framework;
GRANT ALL ON TABLE public.triunfador TO public;

And here is my other table called "project"

  -- Table: public.proyecto

-- DROP TABLE public.proyecto;

CREATE TABLE public.proyecto
(
  id_pro integer NOT NULL DEFAULT nextval('proyecto_id_pro_seq'::regclass),
  nombre_pro character varying(50) NOT NULL,
  trayecto_pro integer NOT NULL,
  anio_pro integer NOT NULL,
  id_asesor integer NOT NULL DEFAULT nextval('proyecto_id_asesor_seq'::regclass),
  id_ald integer NOT NULL DEFAULT nextval('proyecto_id_ald_seq'::regclass),
  CONSTRAINT proyecto_pkey PRIMARY KEY (id_pro),
  CONSTRAINT fkald FOREIGN KEY (id_ald)
      REFERENCES public.aldea (id_ald) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkasesor FOREIGN KEY (id_asesor)
      REFERENCES public.asesor (id_asesor) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.proyecto
  OWNER TO framework;
GRANT ALL ON TABLE public.proyecto TO framework;
GRANT ALL ON TABLE public.proyecto TO public;

-- Index: public.fki_fkald

-- DROP INDEX public.fki_fkald;

CREATE INDEX fki_fkald
  ON public.proyecto
  USING btree
  (id_ald);

-- Index: public.fki_fkasesor

-- DROP INDEX public.fki_fkasesor;

CREATE INDEX fki_fkasesor
  ON public.proyecto
  USING btree
  (id_asesor);

If it is not too much trouble if you can find the solution, it would be very helpful for me to explain in detail how to solve this so that in the future, if I am presented with this situation again, I can easily get out of it. thanks in advance my people.

postgresql

asked by Alfredo Fernández 14.03.2018 в 06:59

source

2 answers

1

You are trying to insert a record in the "triumphant" table that refers to an element that does not exist in the "project" table.

Review the contents of the "project" table.

answered by 14.03.2018 в 08:34

Problem with Counter in JSTL or change for a for (int i) java.util.Listandroid.widget.ImageView "is not supported error android

score 1 · Accepted Answer

Abundant in the response from @Pablo :

The problem is that when you insert the id_pro you give the default value of a sequence:

id_pro integer NOT NULL DEFAULT nextval('triunfador_id_pro_seq'::regclass)

But you specify that id_pro must exist in the project table

CONSTRAINT triunfador_id_pro_fkey FOREIGN KEY (id_pro)
  REFERENCES public.proyecto (id_pro) MATCH SIMPLE

The triunfador_id_pro_seq sequence is independent of the proyecto_id_pro_seq sequence in the project table. You could perfectly have the same id_pro for two winners (this according to your table definition ... by common sense the same project should have only one winner, but that's your thing).

It would be more logical to declare id_pro simply as INTEGER without sequence, and make sure, in your business layer, that the project exists, and otherwise create it. You could do:

INSERT INTO proyecto (id_pro, nombre_pro, trayecto_pro, anio_pro, id_asesor, id_ald) 
VALUES (6, 'nombre', 'trayecto', 2018, 1, 1)
ON CONFLICT (id_pro) DO NOTHING;

INSERT INTO triunfador ...

But you have 2 other problems very similar to your question

First , id_ald has the default value of a sequence and at the same time an FK to aldea.id_ald .

id_ald integer NOT NULL DEFAULT nextval('proyecto_id_ald_seq'::regclass)
...
CONSTRAINT fkald FOREIGN KEY (id_ald) REFERENCES public.aldea (id_ald) MATCH SIMPLE

Again, the proyecto_id_ald_seq sequence will hardly be synchronized with the primary key sequence of the aldea table. If two projects are related to the last village inserted you will also have a foreign key error.

Second , the same goes for id_asesor

id_asesor integer NOT NULL DEFAULT nextval('proyecto_id_asesor_seq'::regclass),
...
CONSTRAINT fkasesor FOREIGN KEY (id_asesor) REFERENCES public.asesor (id_asesor) MATCH SIMPLE

You do not have to assign a sequence as the default value. You have to use your business layer to make sure you insert in proyecto the values of id_asesor and id_aldea that correspond in the referenced table.