多对对关系--参考郝斌老师

如果是两张表是多对多的关系，产生联系，必须借助于两位一张表。比如班级跟老师的关系。一个班级可能有多个老师教，一个老师也可能教多个班级。所以要借助于第三个表。

第三个表跟两张表分表产生关系后，那么两张表就有关系了。

如下代码：

create database aa
 
--创建班级表
create table ClassRoom
(	
	ClassRoom_id int primary key,
	ClassRoom_num int not null,
	ClassRoom_name nvarchar(100)
)
--插入数据
insert into ClassRoom  values(1,20,'java培训1班')
insert into  ClassRoom  values(2,30,'java培训2班')
insert into  ClassRoom  values(3,15,'java培训3班')
insert into  ClassRoom  values(4,50,'java培训4班')



--创建教室表
create table Teacher
(
	Teacher_id int primary key,
	Teacher_name nvarchar(200)
)
--插入数据
insert into Teacher values(1001,'aa')
insert into Teacher values(1002,'bb')
insert into Teacher values(1003,'cc')
insert into Teacher values(1004,'dd')



-- 创建第三张表 来模拟班级和教室的关系 三列的组合的是主键
create table ClassRoom_Teacher_Mapping
(
	ClassRoom_id int not null,
	Teacher_id int not null,
	Course nvarchar(20) not null
) 
--插入数据
insert into ClassRoom_Teacher_Mapping values(1,1001,'JAva')
insert into ClassRoom_Teacher_Mapping values(1,1001,'C')
insert into ClassRoom_Teacher_Mapping values(2,1002,'SSH')
insert into ClassRoom_Teacher_Mapping values(4,1003,'SSH')


--给第三张表（三列的组合）创建主键
alter table ClassRoom_Teacher_Mapping
add constraint pk_Class_Teacher_Course 
primary key(ClassRoom_id,Teacher_id,Course)

--给第三张表添加外键fk_ClassRoom_id 联系跟ClassRoom表的关系 

alter table ClassRoom_Teacher_Mapping
add constraint fk_ClassRoom_id 
foreign key(ClassRoom_id) references ClassRoom(ClassRoom_id)
--给第三张表添加外键 fk_Teacher_id 联系跟 Student 表的关系 
alter table ClassRoom_Teacher_Mapping
add constraint fk_Teacher_id 
foreign key(Teacher_id) references Teacher(Teacher_id)