ORA-02291: ограничение целостности со всеми таблицами, имеющими внешний ключ
Я пытаюсь заполнить свою базу данных, но получаю ORA-02291: ошибка ограничения целостности независимо от того, в каком порядке я пытаюсь вставить данные. Должен ли я заполнить перед выполнением fk(хотя для базы данных это кажется обратным)? Я получаю сообщение об ошибке для каждой вставки в инструкцию. Извините, я новичок в pl/sql. Вот мой код:
--
-- 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', 'bob@mechanical.edu');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
('1', '1', 'bob@mechanical.edu', 'active', 'bob', 'mechanical enginering');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
('900123456', 'alice@student.edu', '50', 'good', 'alice', 'f', '1', 'bob@mechanical.edu');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
('543', '3', 'fluids', '01', 'mechanical enginering', '1', 'bob@mechanical.edu');
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', 'alice@student.edu', '543', '3');
INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
('computer science', 'computer science major', '4', 'jerry@cs.edu');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
('4', '2', 'jerry@cs.edu', 'active', 'jerry', 'computer science');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
('900987654', 'noah@student.edu', '23', 'good', 'noah', 'm', '4', 'jerry@cs.edu');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
('900316487', 'bruce@student.edu', '23', 'probation', 'bruce', 'm', '4', 'jerry@cs.edu');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
('460', '4', 'golang', '01', 'computer science', '4', 'jerry@cs.edu');
INSERT INTO location (building_number, room_number, department_name) VALUES
('43', '209', 'computer science');
INSERT INTO student_took_course VALUES
('900987654', 'noah@student.edu', '460', '4');
INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
('nuclear enginering', 'nuclear major', NULL, '3', 'james@nuckear.edu');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
('3', '7', 'james@nuclear.edu', 'active', 'james', 'nuclear enginering');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
('900876543', 'alex@student.edu', '37', 'probation', 'alex', 'm', '3', 'james@nuclear.edu');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
('845', '2', 'reactors', '01', 'nuclear enginering', '3', 'james@nuclear.edu');
INSERT INTO location (building_number, room_number, department_name) VALUES
('65', '132', 'nuclear enginering');
INSERT INTO student_took_course VALUES
('900876543', 'alex@student.edu', '845', '2');
INSERT INTO department (name, major, minor, chair_id, chair_email) VALUES
('biology', 'biology major', NULL, '8', 'elizabeth@biology.edu');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
('8', '6', 'elizabeth@biology.edu', 'active', 'elizabeth', 'biology');
INSERT INTO faculty (faculty_id, cs_id, faculty_email, status, name, department_name) VALUES
('2', '9', 'dave@biology.edu', 'resigned', 'dave', 'biology');
INSERT INTO student (student_id, student_email, ethnicity, standing, name, gender, faculty_id, faculty_email) VALUES
('900452367', 'paul@student.edu', '05', 'good', 'paul', 'm', '8', 'elizabeth@biology.edu');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
('324', '3', 'micro biology', '01', 'biology', '8', 'elizabeth@biology.edu');
INSERT INTO course (course_number, hours, title, section, department_name, faculty_id, faculty_email) VALUES
('113', '3', 'intro to biology', '01', 'biology', '2', 'dave@biology.edu');
INSERT INTO location (building_number, room_number, department_name) VALUES
('04', '213', 'biology');
INSERT INTO student_took_course VALUES
('900452367', 'paul@student.edu', '324', '4');
INSERT INTO student_took_course VALUES
('900452367', 'paul@student.edu', '113', '2');
2 ответа
Ты можешь использовать
DEFERRABLE INITIALLY DEFERRED опция при создании ограничений внешнего ключа, таких как
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;
чтобы не происходила проверка ограничений во время заполнения таблиц перед их фиксацией , и после того, как весь процесс заполнения завершен прилично, вы можете включить эти ограничения, используя
ENABLE [VALIDATE] вариант, такой как
ALTER TABLE department MODIFY CONSTRAINT department_faculty_fk ENABLE;
ALTER TABLE location MODIFY CONSTRAINT location_department_fk ENABLE;
При взгляде на модель (реконструированную из вашего кода DDL) кажется, что вы можете удалить некоторые ограничения внешнего ключа. Некоторые столбцы тоже можно (вероятно) удалить.
Оригинальная модель
Доработанная модель(потребуются дополнительные настройки!)
Как только код DDL компилируется / выполняется успешно, начните сначала заполнять «родительские таблицы» (т. Е. Таблицы на «одной стороне» отношений). В данном случае: DEPARTMENT, затем LOCATION и FACULTY, затем COURSE и STUDENT, а затем STUDENT_TOOK_COURSE. Если вы пишете INSERT для каждой таблицы как блок операторов, а не смешиваете вставки для различных таблиц, как вы делали изначально, будет легче выявлять ошибки, а также позволит вам успешно запускать все INSERT. .
Пример (с использованием всех вставок ваших вопросов с небольшими изменениями): см. DBfiddle .