[SOLUCIONADO] Problema al insertar llave foránea

[GreenBird]

Hola amigos,

Tengo las siguientes tablas:

Código SQL:

CREATE TABLE tb_mat_materia
(
  mat_id serial NOT NULL,
  mat_materia CHARACTER VARYING(250),
  CONSTRAINT tb_mat_materia_pkey PRIMARY KEY (mat_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tb_mat_materia
  OWNER TO admin;
 

Código SQL:

CREATE TABLE tb_evals_evaluaciones
(
  evals_id serial NOT NULL,
  evals_eval_id INTEGER,
  evals_fecha DATE,
  evals_mat_id INTEGER,
  evals_per_id INTEGER,
  evals_prog_id INTEGER,
  evals_hr_id INTEGER,
  created_at TIMESTAMP(0) WITHOUT TIME zone NOT NULL,
  updated_at TIMESTAMP(0) WITHOUT TIME zone NOT NULL,
  CONSTRAINT tb_evals_evaluaciones_pkey PRIMARY KEY (evals_id),
  CONSTRAINT tb_evals_evaluaciones_evals_hr_id_fkey FOREIGN KEY (evals_hr_id)
      REFERENCES tb_hr_horario (hr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_mat_id_fkey FOREIGN KEY (evals_mat_id)
      REFERENCES tb_mat_materia (mat_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_per_id_fkey FOREIGN KEY (evals_per_id)
      REFERENCES tb_per_personas (per_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_prog_id_fkey FOREIGN KEY (evals_prog_id)
      REFERENCES tb_prog_programas (prog_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tb_evals_evaluaciones
  OWNER TO admin;
 

Pero al momento de ejecutar la siguiente instrucción:

Código SQL:

INSERT INTO tb_evals_evaluaciones (evals_eval_id,evals_fecha,evals_mat_id,evals_per_id,evals_prog_id,evals_hr_id,updated_at,created_at) 
VALUES (1, '10-02-2016', 7, 14, 1, 1, '2016-02-09 17:14:58', '2016-02-09 17:14:58')
 

Me devuelve el siguiente error:

Código SQL:

ERROR:  inserción o actualización en la tabla «tb_evals_evaluaciones» viola la llave FORánea «tb_evals_evaluaciones_evals_mat_id_fkey»
DETALLE:  La llave (evals_mat_id)=(7) no está presente en la tabla «tb_mat_materia».
 

Aclaro que el registro con mat_id=7 sí existe en la tabla tb_mat_materia

El resto de las llaves foráneas que enlazan a otras tablas también ya tienen registros, por lo que solamente con estas dos me esta dando problemas y no sé a qué pueda deberse.

?

[huesos52]

No sabría que decirte. El error es tan explicito como tu explicación.

Se me ocurre que tal vez choquen los tipos de datos. Cuando defines un campo como serial, realmente el tipo de dato asignado es biginteger. Y lo estas cruzando con un integer.

O para efecto de pruebas, prueba esto:

Código SQL:

INSERT INTO tb_evals_evaluaciones (evals_eval_id,evals_fecha,evals_mat_id,evals_per_id,evals_prog_id,evals_hr_id,updated_at,created_at) 
VALUES (1, '10-02-2016','7'::INTEGER, 14, 1, 1, '2016-02-09 17:14:58', '2016-02-09 17:14:58');
 

Estoy asumiendo efectivamente que el registro 7 de la primera tabla existe.

[GreenBird]

Hola huesis52,

Ante todo agradezco mucho que comentes y me des ideas sobre el por qué estaría dando ese problema.

Que probè el insert que me recomendaste y se me prensentó el mismo mensaje de error.

Por último cambié la estructura de la tabla secundaria y nuevamente probé pero siempre el mismo mensaje de error de que no puede hacer el insert.

La estructura de la tabla que probé solamente le cambié el tipo de dato al campo de la llave foránea:

Código SQL:

CREATE TABLE tb_evals_evaluaciones
(
  evals_id serial NOT NULL,
  evals_eval_id INTEGER,
  evals_fecha DATE,
  evals_mat_id BIGINT,
  evals_per_id INTEGER,
  evals_prog_id INTEGER,
  evals_hr_id INTEGER,
  created_at TIMESTAMP(0) WITHOUT TIME zone NOT NULL,
  updated_at TIMESTAMP(0) WITHOUT TIME zone NOT NULL,
  CONSTRAINT tb_evals_evaluaciones_pkey PRIMARY KEY (evals_id),
  CONSTRAINT tb_evals_evaluaciones_evals_hr_id_fkey FOREIGN KEY (evals_hr_id)
      REFERENCES tb_hr_horario (hr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_mat_id_fkey FOREIGN KEY (evals_mat_id)
      REFERENCES tb_mat_materia (mat_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_per_id_fkey FOREIGN KEY (evals_per_id)
      REFERENCES tb_per_personas (per_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_prog_id_fkey FOREIGN KEY (evals_prog_id)
      REFERENCES tb_prog_programas (prog_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tb_evals_evaluaciones
  OWNER TO admin;
 

Y sí, en efecto el registro 7 existe, ya le di un select a la tabla principal y ahí estan todos.

no sé por dónde vendrá el problema, ni idea porque aparentemente la estructura esta "bien"

[GreenBird]

Luego de investigar por ahí creo que sé por dónde viene el error, aclarando que es mi primer proyecto que trabajo con postgresql y una de las cosas que me llamó la atención de este gestor es la psibilidad de particionar tablas, dejo una estructura más completa de lo que tengo, espero me orienten por favor:

Código SQL:

CREATE TABLE tb_mat_materia
(
  mat_id serial NOT NULL,
  mat_materia CHARACTER VARYING(250),
  CONSTRAINT tb_mat_materia_pkey PRIMARY KEY (mat_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tb_mat_materia
  OWNER TO admin;
 
 
CREATE TABLE tb_ing_materia
(
-- Inherited from table tb_mat_materia:  mat_id integer NOT NULL DEFAULT nextval('tb_mat_materia_mat_id_seq'::regclass),
-- Inherited from table tb_mat_materia:  mat_materia character varying(250)
)
INHERITS (tb_mat_materia)
WITH (
  OIDS=FALSE
);
ALTER TABLE tb_ing_materia
  OWNER TO admin;
 
CREATE TABLE tb_evals_evaluaciones
(
  evals_id serial NOT NULL,
  evals_eval_id INTEGER,
  evals_fecha DATE,
  evals_mat_id INTEGER,
  evals_per_id INTEGER,
  evals_prog_id INTEGER,
  evals_hr_id INTEGER,
  created_at TIMESTAMP(0) WITHOUT TIME zone NOT NULL,
  updated_at TIMESTAMP(0) WITHOUT TIME zone NOT NULL,
  CONSTRAINT tb_evals_evaluaciones_pkey PRIMARY KEY (evals_id),
  CONSTRAINT tb_evals_evaluaciones_evals_hr_id_fkey FOREIGN KEY (evals_hr_id)
      REFERENCES tb_hr_horario (hr_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_mat_id_fkey FOREIGN KEY (evals_mat_id)
      REFERENCES tb_mat_materia (mat_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_per_id_fkey FOREIGN KEY (evals_per_id)
      REFERENCES tb_per_personas (per_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tb_evals_evaluaciones_evals_prog_id_fkey FOREIGN KEY (evals_prog_id)
      REFERENCES tb_prog_programas (prog_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tb_evals_evaluaciones
  OWNER TO admin;
 

Como pueden observar tb_mat_materia es una tabla padre de tb_ing_materia, ahora bien, para poblar de datos incialmente lo he realizado por medio de inserts en la tabla tb_ing_materia y como es de esperarse al hacer un "select * from tb_mat_materia" se despliegan todos los registros incluído el registro 7.

Cosa que al ejecutar el:

Código SQL:

INSERT INTO tb_evals_evaluaciones (evals_eval_id,evals_fecha,evals_mat_id,evals_per_id,evals_prog_id,evals_hr_id,updated_at,created_at) 
VALUES (1, '10-02-2016', 7, 14, 1, 1, '2016-02-09 17:14:58', '2016-02-09 17:14:58')
 

Me devuelve:

Código SQL:

ERROR:  inserción o actualización en la tabla «tb_evals_evaluaciones» viola la llave FORánea «tb_evals_evaluaciones_evals_mat_id_fkey»
DETALLE:  La llave (evals_mat_id)=(7) no está presente en la tabla «tb_mat_materia».
 

es decir que aunque la inserción en la tabla padre la he realizado por medio de la tabla hija, la relación (llave primaria - foránea ) la hago directamente en la tabla padre porque cuando quise hacerla en la tabla hija no pude.

Creo que estoy aplicando mal el concepto de herencia de tablas. será que no debo crear relaciones en este caso?

De hecho la única forma que me funcionó el insert fue eliminar la restricción:

Código SQL:

CONSTRAINT tb_evals_evaluaciones_evals_mat_id_fkey FOREIGN KEY (evals_mat_id)
      REFERENCES tb_mat_materia (mat_id) MATCH SIMPLE
 

Viendo estoy creo que estoy concluyendo que no puedo ocupar llaves foráneas que son llaves primarias en tablas padres, en este caso sería de evaluar entonces a qué le doy más peso si a la integridad de los datos o la herencia?

me pueden orientar en este caso por favor?

[huesos52]

Hola

Ejecuté tu script de creación de objetos.
Luego inserté datos en la tabla de materias.
Y no tuvo ningún problema en realizar la inserción.

Código SQL:

INSERT INTO tb_mat_materia (mat_materia) VALUES('Materia1');
INSERT INTO tb_mat_materia (mat_materia) VALUES('Materia2');
INSERT INTO tb_mat_materia (mat_materia) VALUES('Materia3');
INSERT INTO tb_mat_materia (mat_materia) VALUES('Materia4');
INSERT INTO tb_mat_materia (mat_materia) VALUES('Materia5');
INSERT INTO tb_mat_materia (mat_materia) VALUES('Materia6');
INSERT INTO tb_mat_materia (mat_materia) VALUES('Materia7');
 
SELECT *FROM tb_mat_materia;
INSERT INTO tb_evals_evaluaciones (evals_eval_id,evals_fecha,evals_mat_id,evals_per_id,evals_prog_id,evals_hr_id,updated_at,created_at) 
VALUES (1, '10-02-2016', 7, 14, 1, 1, '2016-02-09 17:14:58', '2016-02-09 17:14:58');
 

[GreenBird]

Hola huesos52,

En primera agradecerte tu tiempo en ejecutar el script y hacer pruebas.

en efecto, no da problemas porque los inserts se hacen en la tabla padre, pero si agrego una materia 8 desde la tabla hija:

Código SQL:

INSERT INTO tb_ing_materia (mat_materia) VALUES('Materia8');
SELECT * FROM tb_mat_materia;
 

Y luego trato de hacer el insert con id=8:

Código SQL:

INSERT INTO tb_evals_evaluaciones (evals_eval_id,evals_fecha,evals_mat_id,evals_per_id,evals_prog_id,evals_hr_id,updated_at,created_at) 
VALUES (1, '10-02-2016', 8, 14, 1, 1, '2016-02-09 17:14:58', '2016-02-09 17:14:58');
 

Devuelve el error:

Código SQL:

ERROR:  inserción o actualización en la tabla «tb_evals_evaluaciones» viola la llave FORánea «tb_evals_evaluaciones_evals_mat_id_fkey»
DETALLE:  La llave (evals_mat_id)=(8) no está presente en la tabla «tb_mat_materia».
 

Por ahí he visto que los checks y llaves foráneas como que no funcionan bien cuando se combina con herencia, la única forma que no me de error es eliminando el constrain de la llave foránea.

Creo que es error de concepto mío que no estoy aplicando bien al parecer..

[huesos52]

Ahora entiendo!
Lo que pasa es que estas entendiendo el concepto de herencia desde el punto de vista de los datos y realmente no es así.

La herencia aplica a la estructura y a los campos heredados del mismo, sin tener en cuenta los datos que estas estructuras tengan. Ademas, si te fijas, la foreign key que está creada, apunta realmente a la tabla padre. No tendrías problema de eliminar este constraint y crear uno que apunte a la tabla hija.

[huesos52]

Pero si gustas, puedes contarnos un poco la finalidad del manejo de herencia y tal vez te podamos recomendar una mejor practica.

[GreenBird]

oook!!!

Muchas gracias por ayudarme a aclarar ese concepto.

Me deja la duda cuando me indicas que: "No tendrías problema de eliminar este constraint y crear uno que apunte a la tabla hija."

Te comento que intenté hacer lo siguiente:

Código SQL:

CONSTRAINT tb_evals_evaluaciones_evals_mat_id_fkey FOREIGN KEY (evals_mat_id)
      REFERENCES tb_ing_materia (mat_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
 

Es decir, la llave foránea que apuntara a la tabla hija en lugar de a la tabla padre pero obtuve lo siguiente:

Código SQL:

ERROR:  no hay restricción UNIQUE que coincida con las columnas dadas en la tabla referida «tb_ing_materia»
 
********** Error **********
 
ERROR: no hay restricción UNIQUE que coincida con las columnas dadas en la tabla referida «tb_ing_materia»
SQL state: 42830
 

solamente por probar sí pude hacer una llave foránea entre la tabla padre y la hija, más sin embargo como en lo indico anteriormente no pude.

Pues lo que pretendo hacer es un catálogo de materias que se imparte en una institución educativa, la cual al ser grande, tiene muchas carreras con diferentes versiones de planes de estudio para una misma carrera y pensé en dividir esas materias en pequeños grupos utilizando la herencia, que dicho sea de paso comprendí mal, es decir, si deseo obtener las carreras que son de ingeniería pues agruparlas en la tb_ing_materia, ese era el planteamiento que tenía.

Gracias por el aporte!!

[huesos52]

Me deja la duda cuando me indicas que: "No tendrías problema de eliminar este constraint y crear uno que apunte a la tabla hija."

Greenbird.. buscando en la documentación oficial de postgres me encuentro con esto

All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

http://www.postgresql.org/docs/9.1/s...l-inherit.html

Cuando haces uso de herencia, solo los contraints check y not null son heredados, el resto no.
Y es exactamente lo que te sale en el error. Antes de crear la foreign key, debes crear un primary o unique key manualmente a la tabla hija y posterior a eso, crear el foreign key que acabas de ensayar.

Por lo que pones, no veo muy necesario el uso de herencia, sino mas bien analizar y diseñar un correcto modelo relacional que te soporte lo que quieres. Recuerda que las agrupaciones no necesariamente se deben pensar a nivel de tablas, con un buen uso de SQL se pueden lograr cosas maravillosas con una estructura de almacenamiento optima y adecuada.

Saludos

[GreenBird]

Interesante!!!

Ya veo como es la cuestión en todo esto, huesos52, agradezco mucho tu tiempo en responder, probar mis scripts y a la vez analizar la situación y darme una referencia.

Ha sido de mucho provecho para mí probar con este gestor de base de datos, quiero apostarle a PostgreSQL a todos los nuevos proyectos.

Saludos.