SQL Developer - Integrity Constraint , parent key not found ( while inserting values )

Question

I know that in order to insert values in a table which relies on foreign keys you need to have data in that primary key of that table .

These are my constraints :

 ALTER TABLE DIDACT
   MODIFY (CONSTRAINT id_prof_fk FOREIGN KEY(id_prof) REFERENCES profs (id_prof));

 ALTER TABLE DIDACT
   MODIFY (CONSTRAINT id_course_fk FOREIGN KEY(id_course) REFERENCES courses (id_course));

Next I insert values in both profs and courses tables :

INSERT INTO courses VALUES ('21', 'Logic', 1, 1, 5);
INSERT INTO courses VALUES ('22', 'Math', 1, 1, 4);
INSERT INTO courses VALUES ('23', 'OOP', 1, 2, 5);
INSERT INTO courses VALUES ('24', 'DB', 2, 1, 8);
INSERT INTO courses VALUES ('25', 'Java', 2, 2, 5);

INSERT INTO profs VALUES ('p1', 'Mary', 'Banks', 'Prof');
INSERT INTO profs VALUES ('p2', 'Francis', 'Steven', 'Conf');
INSERT INTO profs VALUES ('p3', 'John', 'Jobs', 'Prof');
INSERT INTO profs VALUES ('p4', 'Alex', 'Brown', 'Prof');
INSERT INTO profs VALUES ('p5', 'Dan', 'Lovelace', 'Lect');
INSERT INTO profs VALUES ('p6', 'Roxanne', 'Smith', 'Conf');

Then I'm trying to populate the DIDACT table:

INSERT INTO didact VALUES ('p1','21');
INSERT INTO didact VALUES ('p3','21');
INSERT INTO didact VALUES ('p5','22');

But this occurs :

INSERT INTO didact VALUES ('p1','21') Error report - SQL Error: ORA-02291: integrity constraint (user.ID_COURSE_FK) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.

These are my tables , in case it will help :

CREATE TABLE courses(
  id_course CHAR(2),
  course_name VARCHAR2(15),
  year NUMBER(1),
  semester NUMBER(1),
  credits NUMBER(2)
  )

CREATE TABLE profs(
  id_prof CHAR(4),
  name CHAR(10),
  surname CHAR(10),
  grade VARCHAR2(5)
  )

CREATE TABLE didact(
  id_prof CHAR(4),
  id_course CHAR(4)
  )

I'm struggling with this for about an hour and I still haven't managed to find my mistake.

Thank you.

As a general rule: never use char(x) unless you really know what you are doing. — a_horse_with_no_name
Why are you using CHAR rather than VARCHAR2 for some of the columns? — Alex Poole
Yeah ... that was a silly mistake I usually use CHAR for faster performance of the db , but I forgot about the fixed length issue and the blanks... — maspinu

piet.t piet.t · Accepted Answer · 2016-02-19T10:27:22

You seem to have different formats for id_course in your tables. In didact it's id_course CHAR(4) and in courses it's id_course CHAR(2).

And since you use a fixed-length type the value in didact will differ from the one in courses by two added blanks.

SQL Developer - Integrity Constraint , parent key not found ( while inserting values )

1 Answers