如何在MySQL中创建关系表?

I have 3 tables, students, courses, enroll with relation as "students enroll in courses"

我有3个表格，学生，课程，和“学生注册课程”的关系

I have created students table with sid as primary key

我用sid创建了学生表作为主键。

I have created courses table with cid as primary key

我创建了以cid为主键的课程表

Now I have to create enroll table which I did as follows, but it shows an error on the keyword references, what is the mistake ?

现在我必须创建一个注册表，如下所示，但是它显示了关键字引用上的一个错误，什么错误?

create table enroll(
    grade char(2),
    sid int not null,
    cid int not null,
    primary key(sid,cid),
    foreign key cid references courses on delete cascade,
    foreign key sid references students on delete cascade
);

3 个解决方案

#1

You have to specify which fields you're referencing in the foreign table, and both sets of key field(s) must be bracketed () as well.

您必须指定在外表中引用的字段，而且两组键字段都必须括起来()。

foreign key (cid) references courses (name_of_foreign_field_here) on delete cascade,
            ^   ^                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

#2

simple piece of advice

简单的建议

for many to many relationship tables you have to make another relation table, and take PK ids of both the tables and save in this relationship table

对于许多关系表，您必须创建另一个关系表，并将两个表的PK id保存在这个关系表中

while for a one to many relationship table put an attribute for the PK of the one table in the attributes of the many table and always link them like that

而对于一个1到多个关系表，在多个表的属性中放置一个属性用于一个表的PK，并始终像这样链接它们

#3

I think you need to put the cid and sid in braces. This is a sample code try it out and let know your result. Please make sure that the size and data type of the references i.e. the primary keys of course and student and that of enroll are the same.

我想你需要把cid和sid套在牙套里。这是一个示例代码，尝试它并让您知道结果。请确保参考文献的大小和数据类型，即课程的主键和学生的主键和注册的主键是相同的。

CREATE TABLE `enroll` (
    `sid` INT(10) NOT NULL,
    `cid` INT(10) NOT NULL,
    PRIMARY KEY (`sid`, `cid`),
    INDEX `FK_enroll_course` (`cid`),
    CONSTRAINT `FK_enroll_course` FOREIGN KEY (`cid`) REFERENCES `course` (`id`) ON DELETE CASCADE,
    CONSTRAINT `FK_enroll_student` FOREIGN KEY (`sid`) REFERENCES `student` (`id`) ON DELETE CASCADE
)

#1