Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    13

    Unanswered: inserting record - first tutorial

    Hello

    I keep getting the error below when I try to insert a record in to an EMPTY table.

    ERROR at line 1:
    ORA-02291: integrity constraint (SYSTEM.FACULTY_LOCID_FK) violated - parent
    key not found

    The record I'm trying to insert has a value of 1 in the faculty_id field. The faculty_id field is also the primary key field. I've made sure that I inserted records that other tables refernce to and also I have no problems inserting records where the faculty_id is 2 or 3 or 4 etc. But I can't insert a record who has a faculty_id of 1?!?! =(


    SQL makeup:


    CREATE TABLE location
    (locid NUMBER(5) CONSTRAINT location_locid_pk PRIMARY KEY,
    bldg_code VARCHAR2(10) CONSTRAINT location_bldg_code_nn NOT NULL,
    room VARCHAR2(6) CONSTRAINT location_room_nn NOT NULL,
    capacity NUMBER(5) CONSTRAINT location_capacity_nn NOT NULL);


    CREATE TABLE faculty
    (faculty_id NUMBER(5) CONSTRAINT faculty_faculty_id_pk PRIMARY KEY,
    flname VARCHAR2(30) CONSTRAINT faculty_flname_nn NOT NULL,
    ffname VARCHAR2(30) CONSTRAINT faculty_ffname_nn NOT NULL,
    fmi CHAR(1),
    locid NUMBER(5) CONSTRAINT faculty_locid_fk REFERENCES location(locid),
    fphone VARCHAR2(10),
    frank CHAR(4) CONSTRAINT faculty_frank_cc
    CHECK ((frank = 'ASSO') OR (frank = 'FULL')
    OR (frank = 'ASST') OR (frank = 'INST')),
    fpin NUMBER(4));

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    It appears that the loc_id you are entering into the faculty table does not exist in the location table.

    In your faculty table you have this....

    locid NUMBER(5) CONSTRAINT faculty_locid_fk REFERENCES location(locid),

    Also, you appear to working in the SYSTEM schema. This is a bad idea!!!. Just to get you going quickly so that you can keep doing everything you need to, run this as SYS

    CREATE USER <youname> IDENTIFIED BY <yourpassword>;
    grant create session to <yourname>;
    grant dba to <yourname>;
    alter user <yourname> default tablespace USERS;
    alter user <yourname> temporary tablespace TEMP;

    And start working in there. I/you will probably get lots of messages about granting dba privs to your new user, but unless you want a full tutorial about Oracle users etc, this is the quickest way to get you working out of the SYSTEM schema.

    The tablespace names might be different, check them using

    select * from dba_tablespaces

    Trust me, you'll save yourself no end of trouble further down the line by getting your work out of SYSTEM.

    Hth
    Bill

  3. #3
    Join Date
    May 2003
    Posts
    87
    billm has told you the right thing to do.

    Also you can try connecting as user "scott", password "tiger". This is the user/schema created with the default database and if this exists, you can play around here. It has also has some sample tables.

    Good Luck !

  4. #4
    Join Date
    May 2002
    Posts
    13
    thanx for your help. i did find out my mistake but i never about working in the system being a bad idea.

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Max,

    Probably right at this time, you're not sure about why working in system is a bad idea, trust me it is. Today I managed a critical database alteration (yes it was critical) purely because nothing had ever been run as SYS/SYSTEM.

    I often send database dumps to people (exp/imp). One day someone sent one back, they had logged in as SYS when they exported it, I wasn't watching what I was doing and completely screwed my dev instance when I imported.

    Oracle is a great database, but if you mess with SYS unintentionally or intentionally you could be stuck with the results for years to come. Other people here probably have many other horror stories to tell

    Cheers
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •