我正在使用mysql并遇到一些混乱。我创建了两个表academy和courses。我需要确定如何构造表字段的帮助。例如one to many架构。一个学院可以提供许多课程,并且可以为很多学院提供一门课程。下表的结构是否正确?
academy
courses
one to many
create table academy ( academy_id int(11) not null auto_increment, course_id int() NOT NULL , name varchar(25) not null, primary key (id), ); CREATE TABLE course ( course_id int(11) not null auto_increment, course_name VARCHAR(50) NOT NULL , primary key (course_id), foreign key (academy_id) REFERENCES academy (academy_id) on delete cascade );
预期结果示例
id Name Course 1 The Alamo School 125 Intro to Programming 2 Bearcat High School 125 Intro to Programming
您真正需要的是用于学院的表,用于课程的表以及一个关系表,您可以在其中存储多对多关系。我留给您查询以获取您正在寻找的结果:)
CREATE TABLE academy ( academy_id int(11) not null auto_increment, name varchar(25) not null, primary key (id), ); CREATE TABLE course ( course_id int(11) not null auto_increment, course_name VARCHAR(50) NOT NULL , primary key (course_id), ); CREATE TABLE accademy_course ( academy_id int(11) not null, course_id int(11) not null , primary key (academy_id, course_id), foreign key (academy_id) REFERENCES academy (academy_id) on delete cascade, foreign key (course_id) REFERENCES course (course_id) on delete cascade );