1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ora-02291 help! :(

Discussion in 'SQL PL/SQL' started by eulogix, Jun 26, 2012.

  1. eulogix

    eulogix Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hey guys wondering if you could help im trying to implement a database and it all goes in well with the below script but when I try to insert data i get an error telling me ORA-02291 and im not sure where ive gone wrong here if anyone could help would be awesome thanks :)!

    Code (SQL):
    CREATE TABLE subject_area (
    subject_id NUMBER(2),
    subject_name varchar2(30),
    CONSTRAINT pk_subject_area PRIMARY KEY (subject_id)
    );

    CREATE TABLE company (
    company_id NUMBER(2),
    company_name varchar2(15),
    city varchar2(12),
    industry varchar2(15),
    no_employees NUMBER(5),
    revenue NUMBER(10),
    training_budget NUMBER(6),
    CONSTRAINT pk_company PRIMARY KEY (company_id)
    );


    CREATE TABLE course (
    course_id NUMBER(3),
    title varchar2(30),
    duration NUMBER(2),
    cost NUMBER(5),
    subject_id NUMBER(2),
    CONSTRAINT pk_course PRIMARY KEY (course_id),
    CONSTRAINT fk_subject
    FOREIGN KEY (subject_id)
    REFERENCES subject_area(subject_id)
    );

    CREATE TABLE instructor (
    instructor_id NUMBER(3),
    instructor_name varchar2(15),
    salary NUMBER(6,2),
    commission NUMBER(6,2),
    mentor_id NUMBER(3) ,
    date_hired DATE,
    CONSTRAINT pk_instructor PRIMARY KEY (instructor_id),
    CONSTRAINT fk_mentor
    FOREIGN KEY (mentor_id)
    REFERENCES instructor(instructor_id)
    );

    CREATE TABLE site (
    site_id NUMBER(2),
    location varchar2(12),
    CONSTRAINT pk_site PRIMARY KEY (site_id)
    );

    CREATE TABLE student (
    student_id NUMBER(4),
    student_fname varchar2(10),
    student_lname varchar2(12),
    tel_no varchar2(15),
    fax_no NUMBER(15),
    gender varchar2(1),
    date_of_birth DATE,
    student_desc varchar2(25),
    preferred_language varchar2(2),
    passport_program varchar2(1),
    company_id NUMBER(2) NOT NULL,
    CONSTRAINT pk_student PRIMARY KEY (student_id),
    CONSTRAINT fk_company
    FOREIGN KEY (company_id)
    REFERENCES company (company_id)
    );


    CREATE TABLE auth (
    instructor_id NUMBER(3) NOT NULL,
    course_id NUMBER(3) NOT NULL,
    auth_date DATE,
    CONSTRAINT pk_auth PRIMARY KEY (instructor_id,course_id),
    CONSTRAINT fk_instructor
    FOREIGN KEY (instructor_id)
    REFERENCES instructor(instructor_id),
    CONSTRAINT fk_course
    FOREIGN KEY (course_id)
    REFERENCES course(course_id)
    );

    CREATE TABLE offering (
    offering_id NUMBER(5),
    course_id NUMBER(3) NOT NULL,
    instructor_id NUMBER(3) NOT NULL,
    site_id NUMBER(2) NOT NULL,
    start_date DATE,
    max_no_students NUMBER(2),
    CONSTRAINT pk_offering PRIMARY KEY (offering_id),
    CONSTRAINT fk_auth
    FOREIGN KEY (instructor_id, course_id)
    REFERENCES auth (instructor_id, course_id),
    CONSTRAINT fk_site
    FOREIGN KEY (site_id)
    REFERENCES site (site_id)
    );

    CREATE TABLE attendance (
    offering_id NUMBER(5) NOT NULL,
    student_id NUMBER(4) NOT NULL,
    evaluation varchar2(1),
    amount_paid NUMBER(6,2),
    CONSTRAINT pk_attendance PRIMARY KEY (offering_id,student_id),
    CONSTRAINT fk_offering
    FOREIGN KEY (offering_id)
    REFERENCES offering (offering_id),
    CONSTRAINT fk_student
    FOREIGN KEY (student_id)
    REFERENCES student(student_id)
    );

    commit;
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    To which you are trying to insert the data and what you have written in that statement. Can you please post them also by that we can elloborate your work and identify the problem where it exists.
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    Before doing this you need to identify foreign keys for which tables they have been set. Please check the following criteria's and follow to insert properly step by step. By that you can insert them successfully.

    You needs to insert "OFFERING" table before inserting into "ATTENDANCE" table and "STUDENT" table.
    You needs to insert "AUTH" and "SITE" tables before inserting into "OFFERING" table.
    You needs to insert "INSTRUCTOR" and "COURSE" tables before inserting into "AUTH" table
    You needs to insert "COMPANY" table before inserting into "STUDENT" table
    You needs to insert "SUBJECT_AREA" table before inserting into "COURSE" table

    This is the hierarchy you need to follow while inserting records.
    1. COMPANY
    2. STUDENT
    3. SUBJECT_AREA
    4. COURSE
    5. INSTRUCTOR
    6. AUTH
    7. SITE
    8. OFFERING
    9. ATTENDANCE


    Insert the records in the tables according to the steps given above.

    Because
    COMPANY Table have no dependencies with other tables
    SUBJECT_AREA Table have no dependencies with other tables
    SITE Table have no dependencies with other tables
    INSTRUCTOR Table also have no dependencies with other tables
    COURSE Table have dependencies like you need to insert into SUBJECT_AREA table before inserting into COURSE table
    STUDENT Table have dependencies like you need to insert into COMPANY table before inserting into STUDENT table
    AUTH Table have dependencies like you need to insert into INSTRUCTOR and COURSE tables before inserting into AUTH table
    OFFERING Table have dependencies like you need to insert into AUTH and SITE Tables before inserting into OFFERING table
    ATTENDANCE Table have dependencies like you need to insert into OFFERING and STUDENT Tables before inserting into ATTENDANCE table

    Please go with foreign key concept by that you can get brief introduction about them. Here is the link for that.
    Foreign key concept in detail
     
  4. eulogix

    eulogix Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thanks! :) when I try to insert to instructor this is the error message I receive
    Code (SQL):
    ORA-02291: integrity CONSTRAINT (SYSTEM.FK_MENTOR) violated - parent KEY NOT found
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are trying to insert a record with a mentor_id that is not loaded into the table; this column references the instructor_id column in the same table so you need to insert records based on existing instructor_id values. In other words you can't have a mentor_id of 7 if the instructor_id of 7 is not inserted yet.
     
    eulogix likes this.
  6. eulogix

    eulogix Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thanks! :) How would I fix my table accordingly? if you could help would be awesome thanks!
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Here for instructor table, it is having self dependency on mentor_id which is referred to instructor_id. So you need to pass mentor_id which is already existing for instructor_id or while inserting you need to provide same value for both of them.
     
    eulogix likes this.
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Is it mandatory to provide foreign key for mentor_id which references to same table of instructor_id?

    If not you can remove that foreign key just by disabling it.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not quite true as a foreign key can contain null values:

    Code (SQL):
    SQL> SELECT * FROM instructor;
    INSTRUCTOR_ID INSTRUCTOR_NAME     SALARY COMMISSION  MENTOR_ID DATE_HIRE
    ------------- --------------- ---------- ---------- ---------- ---------
                1                       2000                       28-JAN-11
                2                       2000                       28-AUG-09
                3                       2000                       28-MAR-08
                4                       2000                       28-OCT-06
                5                       2000                       28-MAY-05
                6                       2000                       28-DEC-03
                7                       2000                       28-JUL-02
                8                       2000                       28-FEB-01
                9                       2000                       28-SEP-99
               10                       2000                       28-APR-98
               11                       2000                       28-NOV-96
               12                       2000                       28-JUN-95
               13                       2000                       28-JAN-94
               14                       2000                       28-AUG-92
               15                       2000                       28-MAR-91
               16                       2000                       28-OCT-89
               17                       2000                       28-MAY-88
               18                       2000                       28-DEC-86
               19                       2000                       28-JUL-85
               20                       2000                       28-FEB-84
               21                       2000                       28-SEP-82
               22                       2000                       28-APR-81
               23                       2000                     1 28-NOV-79
               24                       2000                     2 28-JUN-78
               25                       2000                     3 28-JAN-77
               26                       2000                     4 28-AUG-75
               27                       2000                     5 28-MAR-74
               28                       2000                     6 28-OCT-72
               29                       2000                     7 28-MAY-71
               30                       2000                     8 28-DEC-69
               31                       2000                     9 28-JUL-68
               32                       2000                    10 28-FEB-67
               33                       2000                    11 28-SEP-65
     
    It cannot contain values that cannot be referenced in the primary key so the order of the inserts is important.
     
    eulogix likes this.
  10. eulogix

    eulogix Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Would deleting the foreign key not mess up some queries when run? if not ill give it a try thanks :)!
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Define 'mess up some queries' -- I see no issue with NULL mentor_id values:

    Code (SQL):
    SQL> SELECT instructor_id, mentor_id
      2  FROM instructor
      3  WHERE mentor_id IS NOT NULL;
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
               23          1
               24          2
               25          3
               26          4
               27          5
               28          6
               29          7
               30          8
               31          9
               32         10
               33         11
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
               34         12
               35         13
               36         14
               37         15
               38         16
               39         17
               40         18
               41         19
               42         20
               43         21
               44         22
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
               45         23
               46          1
               47          2
               48          3
               49          4
               50          5
               51          6
               52          7
               53          8
               54          9
               55         10
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
               56         11
               57         12
               58         13
               59         14
               60         15
               61         16
               62         17
               63         18
               64         19
               65         20
               66         21
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
               67         22
               68         23
               69          1
               70          2
               71          3
               72          4
               73          5
               74          6
               75          7
               76          8
               77          9
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
               78         10
               79         11
               80         12
               81         13
               82         14
               83         15
               84         16
               85         17
               86         18
               87         19
               88         20
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
               89         21
               90         22
               91         23
               92          1
               93          2
               94          3
               95          4
               96          5
               97          6
               98          7
               99          8
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
              100          9
              101         10
              102         11
              103         12
              104         13
              105         14
              106         15
              107         16
              108         17
              109         18
              110         19
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
              111         20
              112         21
              113         22
              114         23
              115          1
              116          2
              117          3
              118          4
              119          5
              120          6
              121          7
     
    INSTRUCTOR_ID  MENTOR_ID
    ------------- ----------
              122          8
              123          9
              124         10
              125         11
              126         12
              127         13
              128         14
              129         15
              130         16
              131         17
              132         18
     
    110 ROWS selected.
     
    SQL>
    SQL> COLUMN mentors format a30
    SQL> break ON mentor_id skip 1
    SQL> SELECT mentor_id, lpad(instructor_id, 2
      2  FROM instructor
      3  WHERE mentor_id IS NOT NULL
      4  CONNECT BY mentor_id = prior instructor
      5  START WITH mentor_id IS NULL
      6  ORDER BY 1,2;
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
             1   23
                 46
                 69
                 92
                115
             2   24
                 47
                 70
                 93
                116
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
             3   25
                 48
                 71
                 94
                117
             4   26
                 49
                 72
                 95
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
             4  118
             5   27
                 50
                 73
                 96
                119
             6   28
                 51
                 74
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
             6   97
                120
             7   29
                 52
                 75
                 98
                121
             8   30
                 53
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
             8   76
                 99
                122
             9   31
                 54
                 77
                100
                123
            10   32
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
            10   55
                 78
                101
                124
            11   33
                 56
                 79
                102
                125

     
     MENTOR_ID MENTORS
    ---------- ------------------------------
            12   34
                 57
                 80
                103
                126
            13   35
                 58
                 81
                104
                127
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
            14   36
                 59
                 82
                105
                128
            15   37
                 60
                 83
                106
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
            15  129
            16   38
                 61
                 84
                107
                130
            17   39
                 62
                 85
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
            17  108
                131
            18   40
                 63
                 86
                109
                132
            19   41
                 64
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
            19   87
                110
            20   42
                 65
                 88
                111
            21   43
                 66
                 89
     
     MENTOR_ID MENTORS
    ---------- ------------------------------
            21  112
            22   44
                 67
                 90
                113
            23     45
                   68
                   91
                  114
     
    110 ROWS selected.
     
    SQL>
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which then opens up the possibility of inserting invalid values into the mentor_id column; the rule apparently states that all mentors must be current instructors. Removing the foreign key constraint invalidates that rule.
     
  13. eulogix

    eulogix Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    This is the problem :( any suggestions? thanks for help so far guys it is appreciated :)
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What OTHER suggestions do you need??? The inserts must be properly sequenced so the mentor_id inserted references an existing instructor OR insert NULL values for mentor_id at insert then update the table once the inserts complete. These are the only two solutions available to you by keeping the constraint in force. Both 'suggestions' have been offered in this thread before.
     
    eulogix and Bharat like this.
  15. eulogix

    eulogix Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Ok so basically one of the things I want to do with these tables is to link a list of courses to a subject the user types in I tried using a variable and I cant seem to get one for this working what I've got so far is

    Code (SQL):
    SELECT subject_area.subject_name, course.course_id
    FROM Subject_area, Course,
    WHERE subject_area.subject_name = &subject;
    it's driving me insane! i'm quite new to SQL so sorry for the nooby questions lol
     
  16. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    You need to give link between course table and subject_area while you are using two tables in a query. Just like join. Here is the query what you are trying for.

    Code (Text):


    SELECT subject_area.subject_name, course.course_id
    FROM subject_area, course
    WHERE subject_area.subject_name = &subject
        and subject_area.subject_id = course.subject_id;


     
    Like this you need to join two tables. This is the major theme of foreign keys. You need to match subject_id from subject_area and course tables to get course details for subject what you are trying for.
     
  17. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That example is missing the single quotes around the variable so it won't return anything except an error:

    Code (SQL):
    SQL> SELECT subject_area.subject_name, course.course_id
      2  FROM subject_area, course
      3  WHERE subject_area.subject_name = &subject
      4      AND subject_area.subject_id = course.subject_id;
    OLD   3: WHERE subject_area.subject_name = &subject
    NEW   3: WHERE subject_area.subject_name = math
    WHERE subject_area.subject_name = math
                                      *
    ERROR at line 3:
    ORA-00904: "MATH": invalid identifier

    SQL>
    SQL> SELECT subject_area.subject_name, course.course_id
      2  FROM subject_area, course
      3  WHERE subject_area.subject_name = '&subject'
      4      AND subject_area.subject_id = course.subject_id;
    OLD   3: WHERE subject_area.subject_name = '&subject'
    NEW   3: WHERE subject_area.subject_name = 'math'
     
    no ROWS selected
     
    SQL>
    SQL> SELECT sa.subject_name, c.course_id
      2  FROM subject_area sa JOIN course c ON (c.subject_id = sa.subject_id)
      3  WHERE UPPER(subject_name) = UPPER('&subject');
    OLD   3: WHERE UPPER(subject_name) = UPPER('&subject')
    NEW   3: WHERE UPPER(subject_name) = UPPER('math')
     
    SUBJECT_NAME                    COURSE_ID
    ------------------------------ ----------
    Math                                   10
    Math                                   20
    Math                                   30
    Math                                   40
    Math                                   50
    Math                                   60
    Math                                   70
    Math                                   80
    Math                                   90
    Math                                  100
     
    10 ROWS selected.
     
    SQL>
    Placing single quotes around the variable name allows the query to run without error, but it doesn't return the desired rows. The third example does return data as it takes into account the case of the input text and the case of the database value.