Help - Search - Members - Calendar
Full Version: Help with Insert Into Statement
Oracle DBA Forums > Oracle > Oracle Forum
Enrique Hernandez
I am creating the following two tables...no issues here:
CODE
CREATE TABLE COURSE_SECTION
  (
  Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key,
  Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE,
  Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
  Secnum NUMBER(2) NOT NULL,
  Fid NUMBER(4) CONSTRAINTS COURSE_SELECTION_Fid REFERENCES FACULTY,
  Day VARCHAR2(10),
  Locid NUMBER(5) CONSTRAINTS COURSE_SELECTION_Locid REFERENCES LOCATION,
  Maxenrl NUMBER(4) NOT NULL,
  Currenrl NUMBER(4) NOT NULL
  );



CREATE TABLE ENROLLMENT
  (
  Sid Number(5) CONSTRAINTS ENROLLMENT_Sid_fk REFERENCES STUDENT,
  Csecid NUMBER(8) CONSTRAINTS ENROLLMENT_csecid_fk REFERENCES COURSE_SECTION,
  Grade CHAR(1) CHECK(Grade IN ('A', 'B', 'C', 'D', 'F', 'I', 'W')),
  cONSTRAINTS ENROLLMENT_Sid_Csecid PRIMARY KEY (Sid, Csecid)
  );


The issue I am having is actually inserting data into the table:
CODE
INSERT INTO ENROLLMENT
  VALUES (100, 1000, 'A' );
INSERT INTO ENROLLMENT
  VALUES (100, 1003, 'A' );
INSERT INTO ENROLLMENT
  VALUES (101, 1000, 'C' );
INSERT INTO ENROLLMENT
  VALUES (102, 1000, 'C' );
INSERT INTO ENROLLMENT
  VALUES (102, 1001, NULL );
INSERT INTO ENROLLMENT
  VALUES (102, 1003, 'I' );


But I get an ORACLE error of

ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not found

How can the parent key not be found when I have it declared/created in the above statement?
burleson
Hi Enrique,

>>> How can the parent key not be found when I have it declared/created in the above statement?

But you did not insert the value in the course_section table!

When you declared the foreign key, you told Oracle not to insert an "orphan", a row that does not have a matching parent row in the course_section table.

**************************************
>> ORA-02291 - integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not found

To see an error, go to the OS prompt and enter "oerr ora 02291"

QUOTE
ORA-02291: integrity constraint (string.string) 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.


http://www.dba-oracle.com/t_ora_02291_inte...y_not_found.htm


Enrique, it looks like you can benefit from ordering the Easy Oracle Pack:

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy
Enrique Hernandez
I am still not following your response. What do I need to change so that I will not get the eror?

It should have a value to insert into..this is the data that was inserted into my course_section table
CODE
INSERT INTO COURSE_SECTION VALUES
  (1000, 1, 2, 1, 12, ‘MWF’, 55, 100, 35 );
INSERT INTO COURSE_SECTION VALUES
  (1001, 1, 2, 2, 10, ‘TTH’, 54, 45, 35 );
INSERT INTO COURSE_SECTION VALUES
  (1002, 2, 2, 3, 10, ‘MWF’, 53, 35, 32 );
INSERT INTO COURSE_SECTION VALUES
  (1003, 3, 2, 1, 11, ‘TTH’, 54, 45, 35 );
burleson
This is a DBA forum!

We don;t answer developer questions here.

Your problem is that you have not attended the cumplsory Oracle University training. . .

>> 'MWF', "you have leaning quote issues again".

Insert the course rows first and isloate which row throws the error by inserting one at a time. If the parent xists, the other will insert.

If you want a tutot, please conrtact Oracle technical support. That what your license fee pays for, tech support:

http://support.oracle.com

Good Luck!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.