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?!?! =(
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,
locid NUMBER(5) CONSTRAINT faculty_locid_fk REFERENCES location(locid),
frank CHAR(4) CONSTRAINT faculty_frank_cc
CHECK ((frank = 'ASSO') OR (frank = 'FULL')
OR (frank = 'ASST') OR (frank = 'INST')),
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.
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.
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