如何创建父和两个子表mysql

时间:2022-10-05 01:02:21

I have a parent table named grade:

我有一个名为grade的父表:

classid classname 
   1     classone
   2     classtwo

Two child tables named classone and classtwo.

两个名为classone和classtwo的子表。

classone table:

分类表:

studentid  studentname
   1        john
   2        snow

classtwo table:

classtwo表:

studentid studentname
   1         Tyrion
   2         Lannister

How to create these tables with primary key and foreign key?

如何使用主键和外键创建这些表?

I am newer in mysql database, Who can help me ?

我是mysql数据库的新手,谁能帮帮我?

1 个解决方案

#1


1  

Add FOREIGN KEY (classId) REFERENCES Grade(classId) to each foreign table:

添加FOREIGN KEY(classId)REFERENCES为每个外表提供Grade(classId):

CREATE TABLE Grade(
    classId INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(classId),
    className VARCHAR(255),
) ENGINE=INNODB;

CREATE TABLE Classone(
    studentId  INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(studentId),
    studentName VARCHAR(255),
    classId CHAR(40),
    FOREIGN KEY (classId) REFERENCES Grade(classId)
) ENGINE=INNODB;

CREATE TABLE Classtwo(
   studentId  INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(studentId),
    studentName VARCHAR(255),
    classId CHAR(40),
    FOREIGN KEY (classId) REFERENCES Grade(classId)
) ENGINE=INNODB;

Remember, you also need to add classId in each foreing table to match them. So i also added two colums for these class tables.

请记住,您还需要在每个foreing表中添加classId以匹配它们。所以我还为这些类表添加了两个列。

#1


1  

Add FOREIGN KEY (classId) REFERENCES Grade(classId) to each foreign table:

添加FOREIGN KEY(classId)REFERENCES为每个外表提供Grade(classId):

CREATE TABLE Grade(
    classId INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(classId),
    className VARCHAR(255),
) ENGINE=INNODB;

CREATE TABLE Classone(
    studentId  INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(studentId),
    studentName VARCHAR(255),
    classId CHAR(40),
    FOREIGN KEY (classId) REFERENCES Grade(classId)
) ENGINE=INNODB;

CREATE TABLE Classtwo(
   studentId  INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(studentId),
    studentName VARCHAR(255),
    classId CHAR(40),
    FOREIGN KEY (classId) REFERENCES Grade(classId)
) ENGINE=INNODB;

Remember, you also need to add classId in each foreing table to match them. So i also added two colums for these class tables.

请记住,您还需要在每个foreing表中添加classId以匹配它们。所以我还为这些类表添加了两个列。