我完全陷入了sql oracle数据库。
我想在我的桌面课程中添加:
Insert into Course_Section (CsectionID, MaxCapacity, CourseID, TermID, LocID)
values('14', '10', '2', '1', '1');
和
Insert into Course_Section (CsectionID, MaxCapacity, CourseID, TermID, LocID)
values('15', '10', '3', '1', '1');
当我试图插入第一个时,它告诉我找不到父密钥。
所以我试着在我的表课程中创建两行,这两行的课程ID和我以后使用的相同,但它不起作用…
抱歉,我还是个初学者:)
Create table Course
(
CourseName Varchar2(100) not null,
CourseID number(6) PRIMARY KEY,
Credits number(6)
);
Create table TERM
(
TermID number(6) PRIMARY KEY,
Description Varchar2(100) not null
)
;
Create table LOCATION
(
LocID number(6) PRIMARY KEY,
Building Varchar2(100) NOT NULL,
Room Number(4)
)
;
Create table Student
(
StudID number(6) PRIMARY KEY,
Sname Varchar2(100) NOT NULL,
Birthdate DATE
)
;
Create table Enrollment
(
StudID number(6) PRIMARY KEY,
CSectionID number(6),
FOREIGN KEY (CsectionID) references Student (StudID),
GRADE number(2)
)
;
Create table Course_Section
(
CSectionID number(6) PRIMARY KEY,
MaxCapacity number(3) NOT NULL,
CourseID number(6),
FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),
TermID number(6),
Foreign Key (TERMID) references TERM (termID),
LocID number(6),
Foreign Key (LocID) references Location (locID)
)
;
Insert into COURSE (CourseName, CourseID, Credits)
values('Base de données', '1', '3');
最佳答案
问题是COURSE
表中没有一行的COURSEID
在COURSE
表中有匹配的行。要执行此操作,您需要在COURSE
表中为COURSEID
2和3创建一行:
INSERT INTO COURSE (COURSENAME, COURSEID, CREDITS)
VALUES ('ORGANIC CHEMISTRY', 2, 4);
INSERT INTO COURSE (COURSENAME, COURSEID, CREDITS)
VALUES ('INORGANIC CHEMISTRY', 3, 5);
您还需要提交在执行这些插入时自动启动的事务:
COMMIT;
祝你好运。
****编辑****
还定义了其他外键约束,但由于它们都没有命名,因此很难找出违反了哪些约束。我已经修改了您的ddl以包含每个约束的名称:
Create table Course
(CourseName Varchar2(100) not null,
CourseID number(6) CONSTRAINT PK_COURSE PRIMARY KEY,
Credits number(6));
Create table TERM
(TermID number(6) CONSTRAINT PK_TERM PRIMARY KEY,
Description Varchar2(100) not null);
Create table LOCATION
(LocID number(6) CONSTRAINT PK_LOCATION PRIMARY KEY,
Building Varchar2(100) NOT NULL,
Room Number(4));
Create table Student
(StudID number(6) CONSTRAINT PK_STUDENT PRIMARY KEY,
Sname Varchar2(100) NOT NULL,
Birthdate DATE);
Create table Enrollment
(StudID number(6) CONSTRAINT PK_ENROLLMENT PRIMARY KEY,
CSectionID number(6),
CONSTRAINT ENROLLMENT_FK1
FOREIGN KEY (CsectionID) references Student (StudID),
GRADE number(2));
Create table Course_Section
(CSectionID number(6) CONSTRAINT PK_COURSE_SECTION PRIMARY KEY,
MaxCapacity number(3) NOT NULL,
CourseID number(6),
CONSTRAINT COURSE_SECTION_FK1
FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),
TermID number(6),
CONSTRAINT COURSE_SECTION_FK2
Foreign Key (TERMID) references TERM (termID),
LocID number(6),
CONSTRAINT COURSE_SECTION_FK3
Foreign Key (LocID) references Location (locID));
Insert into COURSE (CourseName, CourseID, Credits)
values('Base de données', '1', '3');
INSERT INTO COURSE (COURSENAME, COURSEID, CREDITS)
VALUES ('ORGANIC CHEMISTRY', 2, 4);
INSERT INTO COURSE (COURSENAME, COURSEID, CREDITS)
VALUES ('INORGANIC CHEMISTRY', 3, 5);
现在,当您试图将行插入
COURSE_SECTION
时,您可以看到违反了哪个约束。(在本例中,它是COURSE_SECTION_FK3
,这意味着LOCATION
表中缺少一行)。SQLFiddle here
祝你好运。