ORA-02291: integrity constraint with all tables having foreign key

Question

I am trying to populate my database but get ORA-02291: integrity constraint error no matter what order I try to insert data. Should I populate before making the fk(although this seems backwards for a database)? I get the error for every single insert into statement. Sorry I am new at pl/sql. Here is my code:

--
-- department Table
--
CREATE TABLE department (
    name         VARCHAR2(45) NOT NULL,
    major        VARCHAR2(45) NOT NULL,
    minor        VARCHAR2(45),
    chair_id     NUMBER(10)   NOT NULL,
    chair_email  VARCHAR2(45) NOT NULL
);

ALTER TABLE department ADD CONSTRAINT departrment_pk PRIMARY KEY ( name );


--
-- faculty TABLE
--
CREATE TABLE faculty (
    faculty_id     NUMBER(10)   NOT NULL,
    cs_id          VARCHAR2(45) NOT NULL,
    faculty_email  VARCHAR2(45) NOT NULL,
    status         VARCHAR2(45) NOT NULL,
    name           VARCHAR2(45) NOT NULL,
    department_name  VARCHAR2(45) NOT NULL
);

ALTER TABLE faculty ADD CONSTRAINT faculty_pk PRIMARY KEY ( faculty_id,
                                                            faculty_email );



--
-- student TABLE
--
CREATE TABLE student (
    student_id     NUMBER(10)   NOT NULL,
    student_email  VARCHAR2(45) NOT NULL,
    ethnicity      NUMBER(10)   NOT NULL,
    standing       VARCHAR2(45) NOT NULL,
    name           VARCHAR2(45) NOT NULL,
    gender         VARCHAR2(45) NOT NULL,
    faculty_id     NUMBER(10)   NOT NULL,
    faculty_email  VARCHAR2(45) NOT NULL
);

ALTER TABLE student ADD CONSTRAINT student_pk PRIMARY KEY ( student_id,
                                                            student_email );



--
-- course TABLE
--
CREATE TABLE course (
    course_number         NUMBER(10)   NOT NULL,
    hours            NUMBER(10)   NOT NULL,
    title            VARCHAR2(45) NOT NULL,
    section          NUMBER(10)   NOT NULL,
    department_name  VARCHAR2(45) NOT NULL,
    faculty_id       NUMBER(10)   NOT NULL,
    faculty_email    VARCHAR2(45) NOT NULL
);

ALTER TABLE course ADD CONSTRAINT course_pk PRIMARY KEY ( course_number );


--
-- location TABLE
--
CREATE TABLE location (
    building_number         NUMBER(10)   NOT NULL,
    room_number             NUMBER(10)   NOT NULL,
    department_name  VARCHAR2(45) NOT NULL
);


--
-- student_took_course TABLE
--
CREATE TABLE student_took_course (
    student_id     NUMBER(10)   NOT NULL,
    student_email  VARCHAR2(45) NOT NULL,
    course_number  NUMBER(10)   NOT NULL,
    grade          NUMBER(10)
);

--
-- create forgein keys
--
ALTER TABLE course
    ADD CONSTRAINT course_department_fk FOREIGN KEY ( department_name )
        REFERENCES department ( name )
            ON DELETE CASCADE;

ALTER TABLE course
    ADD CONSTRAINT course_faculty_fk FOREIGN KEY ( faculty_id,
                                                   faculty_email )
        REFERENCES faculty ( faculty_id,
                             faculty_email )
            ON DELETE CASCADE;

ALTER TABLE student_took_course
    ADD CONSTRAINT course_number_fk FOREIGN KEY ( course_number )
        REFERENCES course ( course_number )
            ON DELETE CASCADE;

ALTER TABLE department
    ADD CONSTRAINT department_faculty_fk FOREIGN KEY ( chair_id,
                                                       chair_email )
        REFERENCES faculty ( faculty_id,
                             faculty_email );

ALTER TABLE faculty
    ADD CONSTRAINT faculty_department_fk FOREIGN KEY ( department_name )
        REFERENCES department ( name );

ALTER TABLE location
    ADD CONSTRAINT location_department_fk FOREIGN KEY ( department_name )
        REFERENCES department ( name );

ALTER TABLE student
    ADD CONSTRAINT student_faculty_fk FOREIGN KEY ( faculty_id,
                                                    faculty_email )
        REFERENCES faculty ( faculty_id,
                             faculty_email );

ALTER TABLE student_took_course
    ADD CONSTRAINT student_id_fk FOREIGN KEY ( student_id,
                                               student_email )
        REFERENCES student ( student_id,
                             student_email )
            ON DELETE CASCADE;


--
-- insert info
--
INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
    ('mechanical enginering', 'mechanical major', 'mechanical minor', '1', '[email protected]');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('1', '1', '[email protected]', 'active', 'bob', 'mechanical enginering');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
    ('900123456', '[email protected]', '50', 'good', 'alice', 'f', '1', '[email protected]');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
    ('543', '3', 'fluids', '01', 'mechanical enginering', '1', '[email protected]');
INSERT INTO location (building_number, room_number, department_name) VALUES
    ('12', '103', 'mechanical engineering');
INSERT INTO student_took_course (student_id, student_email, course_number, grade) VALUES
    ('900123456', '[email protected]', '543', '3');

INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
    ('computer science', 'computer science major', '4', '[email protected]');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('4', '2', '[email protected]', 'active', 'jerry', 'computer science');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
    ('900987654', '[email protected]', '23', 'good', 'noah', 'm', '4', '[email protected]');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
    ('900316487', '[email protected]', '23', 'probation', 'bruce', 'm', '4', '[email protected]');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
    ('460', '4', 'golang', '01', 'computer science',  '4', '[email protected]');
INSERT INTO location (building_number, room_number, department_name) VALUES
    ('43', '209', 'computer science');
INSERT INTO student_took_course VALUES
    ('900987654', '[email protected]', '460', '4');

INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
    ('nuclear enginering', 'nuclear major', NULL, '3', '[email protected]');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('3', '7', '[email protected]', 'active', 'james', 'nuclear enginering');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
    ('900876543', '[email protected]', '37', 'probation', 'alex', 'm', '3', '[email protected]');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
    ('845', '2', 'reactors', '01', 'nuclear enginering', '3', '[email protected]');
INSERT INTO location (building_number, room_number, department_name) VALUES
    ('65', '132', 'nuclear enginering');
INSERT INTO student_took_course VALUES
    ('900876543', '[email protected]', '845', '2');

INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
    ('biology', 'biology major', NULL, '8', '[email protected]');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('8', '6', '[email protected]', 'active', 'elizabeth', 'biology');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
    ('2', '9', '[email protected]', 'resigned', 'dave', 'biology');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
    ('900452367', '[email protected]', '05', 'good', 'paul', 'm', '8', '[email protected]');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
    ('324', '3', 'micro biology', '01', 'biology', '8', '[email protected]');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
    ('113', '3', 'intro to biology', '01', 'biology', '2', '[email protected]');
INSERT INTO location (building_number, room_number, department_name) VALUES
    ('04', '213', 'biology');
INSERT INTO student_took_course VALUES
    ('900452367', '[email protected]', '324', '4');
INSERT INTO student_took_course VALUES
    ('900452367', '[email protected]', '113', '2');

Answer 1

You can use DEFERRABLE INITIALLY DEFERRED option when creating foreign key contraints such as

ALTER TABLE department
    ADD CONSTRAINT department_faculty_fk FOREIGN KEY ( chair_id, chair_email )
        REFERENCES faculty ( faculty_id, faculty_email ) 
  DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE location
    ADD CONSTRAINT location_department_fk FOREIGN KEY ( department_name )
        REFERENCES department ( name )
        DEFERRABLE INITIALLY DEFERRED;

in order not to happen constraint validation during the population of the tables before commiting them, and after all of the population process are completed decently, you can enable those constraints by using ENABLE [VALIDATE] option such as

ALTER TABLE department MODIFY CONSTRAINT department_faculty_fk ENABLE;
ALTER TABLE location MODIFY CONSTRAINT location_department_fk ENABLE;

Answer 2

When looking at the model (reverse-engineered from your DDL code), it appears that you could remove some of the foreign key constraints. Some columns can (probably) be removed, too.

Original model

Modified model (will need some more tweaks!)

Once the DDL code compiles/executes successfully, start populating the "parent tables" first (ie the tables on the "one side" of relationships). In this case: DEPARTMENT, then LOCATION and FACULTY, then COURSE and STUDENT, and then STUDENT_TOOK_COURSE. If you write INSERTs for each table as a block of statements as it were - rather than mixing the inserts for the various tables, as you have done originally - it should be easier to spot mistakes, and should also allow you to run all INSERTs successfully.

Example (using all of the INSERTs of your questions, with a few adjustments): see DBfiddle.