添加主键/外键并引用其他表

Im having some troubles with key referencing. The Error comes from when i try to insert data into Table Mark

我有一些关键参考的麻烦。当我尝试将数据插入表标记时,错误来自

INSERT INTO Mark(examID, studentID, result, occured, noOFAttempts) 
    VALUES ('B10', '1', '67', '11-JUL-07', '1');

I get the error:

我收到错误:

 integrity constraint  violated - parent key
not found

Context :

The tables Exam and Student represent data about college exams and students. The exam results for the students, including the number of attempts a student has had at an exam (noOfAttempts), are recorded in table Mark by using the id column from Exam and the id column from Student. Both id columns have unique values . A student has only the latest result recorded for each exam.

表格考试和学生代表有关大学考试和学生的数据。学生的考试成绩,包括学生在考试中的尝试次数(noOfAttempts),使用Exam中的id列和Student的id列记录在表Mark中。两个id列都具有唯一值。学生只有每个考试记录的最新成绩。

Write a SQL command to create the Mark table. Include the primary keys and foreign keys apparent in the tables specified above.

编写SQL命令以创建Mark表。包括上面指定的表中明显的主键和外键。

CREATE TABLE Exam (
 id VARCHAR(255),
 subject VARCHAR(255),
 noOfStudents INT,
 PRIMARY KEY (id));

CREATE TABLE Student (
id INT,
name VARCHAR(255),
PRIMARY KEY (id));

CREATE TABLE Mark (
examID VARCHAR(255),
studentID INT,
result INT,
occured DATE,
noOFAttempts VARCHAR(255),
FOREIGN KEY (noOFAttempts) REFERENCES Exam(id), 
FOREIGN KEY (noOFAttempts) REFERENCES Student(id));

How do i fix the error i know its to do with wrong referencing, thanks

我如何修复错误,我知道它与错误的引用有关,谢谢

1 个解决方案

#1

Some of the logic behind the Mark table makes sense to me. It relates exams to the students who took those exams. But the motivation to make noOfAttempts a foreign key does not seem to serve much purpose. There are two foreign keys in that table, examID and studentID, and the combination of these two fields is also a primary key. Here is what the Mark definition might look like to avoid these errors:

Mark表背后的一些逻辑对我来说很有意义。它将考试与参加考试的学生联系起来。但是,让noOfAttempts成为外键的动机似乎没有多大用处。该表中有两个外键,examID和studentID,这两个字段的组合也是主键。以下是Mark定义可能会避免这些错误:

CREATE TABLE Mark (
    examID VARCHAR(255),
    studentID INT,
    result INT,
    occured DATE,
    noOFAttempts VARCHAR(255),
    FOREIGN KEY (examID) REFERENCES Exam(id), 
    FOREIGN KEY (studentID) REFERENCES Student(id),
    PRIMARY KEY (examID, studentID)
)

Again, I don't see the point of making noOfAttempts a key of any kind, rather I think it should just be one regular column in the Mark table.

同样,我没有看到noOfAttempts成为任何类型的密钥,而我认为它应该只是Mark表中的一个常规列。

Edit per request from Gordon:

根据戈登的要求编辑:

When you made your insert, you attempted to create a record in Mark which referred to parent records which did not exist. In the case of your original table, you attempted to insert '1' as the noOfAttempts, but this ID did not exist in either the Exam and/or Student tables.

您进行插入时,尝试在Mark中创建一条记录,该记录引用了不存在的父记录。对于原始表,您尝试插入'1'作为noOfAttempts,但此ID在Exam和/或Student表中不存在。

#1

CREATE TABLE Mark (
    examID VARCHAR(255),
    studentID INT,
    result INT,
    occured DATE,
    noOFAttempts VARCHAR(255),
    FOREIGN KEY (examID) REFERENCES Exam(id), 
    FOREIGN KEY (studentID) REFERENCES Student(id),
    PRIMARY KEY (examID, studentID)
)