建立学生表模型
表名:student
字段名 | 数据类型 | 字段描述 |
s_id | int | 主键,自动增长 |
s_name | varchar(20) | 学生姓名 |
s_code | varchar(20) | 学号 |
点击对应的表图标,将之拖入到主体图纸上。
双击表头“MySQL Table”,可以编辑表名。
在字段上点击右键,选“列修改”,可以设置列属性。
改成以下样子
点击"应用"。
编辑完成后
在其上点击右键,选择“选中sql”,可以看到弹出窗口自动生成建表语句
CREATE TABLE IF NOT EXISTS student ( s_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, s_name VARCHAR(20) NOT NULL default ‘’, s_code VARCHAR(20) NOT NULL default ‘’ );
建立学生辅助信息表模型
表名:studentAdd
字段名 | 数据类型 | 字段描述 |
a_id | int | 主键,自动增长 |
s_id | int | 学生id |
s_address | varchar(60) | 学生住址 |
仿照上面流程,完成此表的模型。
然后点击“1对1关系”图标。
将两个端点分别拖动到student.s_id和studentAdd.s_id上去。
点击 数据库 -> MySQL -> 全部SQL 就可以看到完整的sql建表语句
CREATE TABLE IF NOT EXISTS student ( s_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, s_name VARCHAR(20) NOT NULL default ‘’, s_code VARCHAR(20) NOT NULL default ‘’ ); CREATE TABLE IF NOT EXISTS studentAdd ( a_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, s_id INT(10) UNSIGNED NOT NULL default 0, s_address VARCHAR(60) NOT NULL default ‘’ ); alter table studentAdd add constraint studentAdd_s_id_fk0 foreign key (s_id) references student (s_id);
在这里针对1对1关系,建立了外键关联。
建立班级信息表模型
表名:class
字段名 | 数据类型 | 字段描述 |
c_id | int | 主键,自动增长 |
c_name | varchar(60) | 班级名称 |
表名:classStudent
字段名 | 数据类型 | 字段描述 |
cs_id | int | 主键,自动增长 |
c_id | int | 班级ID |
s_id | int | 学生ID |
仿照上面流程,完成此表的模型。
并建立class.c_id 与 classStudent.c_id 的1对多关联,及student.s_id 与 classStudent.s_id的关联
再看完整的建表sql
CREATE TABLE IF NOT EXISTS student ( s_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, s_name VARCHAR(20) NOT NULL default ‘’, s_code VARCHAR(20) NOT NULL default ‘’ ); CREATE TABLE IF NOT EXISTS studentAdd ( a_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, s_id INT(10) UNSIGNED NOT NULL default 0, s_address VARCHAR(60) NOT NULL default ‘’ ); alter table studentAdd add constraint studentAdd_s_id_fk0 foreign key (s_id) references student (s_id); CREATE TABLE IF NOT EXISTS class ( c_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c_name VARCHAR(20) NOT NULL default '' ); CREATE TABLE IF NOT EXISTS classStudent ( cs_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c_id INT(10) NOT NULL, s_id INT(10) NOT NULL ); alter table classStudent add constraint classStudent_c_id_fk0 foreign key (c_id) references class (c_id); alter table classStudent add constraint classStudent_s_id_fk0 foreign key (s_id) references student (s_id);
建立课程班级信息表模型
表名:course
字段名 | 数据类型 | 字段描述 |
co_id | int | 主键,自动增长 |
co_name | varchar(60) | 课程名称 |
表名:courseClass
字段名 | 数据类型 | 字段描述 |
cc_id | int | 主键,自动增长 |
co_id | int | 课程ID |
c_id | int | 班级ID |
仿照上面流程,完成此表的模型。
并建立course.co_id 与 courseClass.co_id 的1对多关联,及class.c_id 与 courseClass.c_id的关联。
导出完整建表sql
CREATE TABLE IF NOT EXISTS student ( s_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, s_name VARCHAR(20) NOT NULL default ‘’, s_code VARCHAR(20) NOT NULL default ‘’ ); CREATE TABLE IF NOT EXISTS studentAdd ( a_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, s_id INT(10) UNSIGNED NOT NULL default 0, s_address VARCHAR(60) NOT NULL default ‘’ ); alter table studentAdd add constraint studcentAdd_s_id_fk0 foreign key (s_id) references student (s_id); CREATE TABLE IF NOT EXISTS class ( c_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c_name VARCHAR(20) NOT NULL default '' ); CREATE TABLE IF NOT EXISTS classStudent ( cs_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c_id INT(10) UNSIGNED NOT NULL, s_id INT(10) UNSIGNED NOT NULL ); alter table classStudent add constraint classStudent_c_id_fk0 foreign key (c_id) references class (c_id); alter table classStudent add constraint classStudent_s_id_fk0 foreign key (s_id) references student (s_id); CREATE TABLE IF NOT EXISTS course ( co_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, co_name VARCHAR(20) NOT NULL default '' ); CREATE TABLE IF NOT EXISTS courseClass ( cc_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, co_id INT(20) UNSIGNED NOT NULL default '', c_id INT(20) UNSIGNED NOT NULL default '' ); alter table courseClass add constraint courseClass_co_id_fk0 foreign key (co_id) references course (co_id); alter table courseClass add constraint courseClass_c_id_fk0 foreign key (c_id) references class (c_id);
3. 外键约束与级联操作🍒
外键约束(FOREIGN KEY)用来在两个表的数据之间建立连接,它可以是一列或者多列,一个表可以有一个或者多个外键。
**外键是表的一个字段,不是本表的主键,但对应另一个表的主键。**定义外键后,不允许删除另一个表中具有关联关系的行。
外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键的值必须等于另一个表中主键的某个值。
外键的主要作用是保持数据的一致性、完整性。
例如,部门表 tb_dept 的主键是 id,在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。
主表(父表): 对于两个具有关联关系的表而言,相关联中字段中的主键所在的表就是主表。
从表(子表): 对于两个具有关联关系的表而言,相关联字段中的外键所在的表就是子表。
设置MySQL外键结束的约束
定义MySQL外键的时候,需要遵守一下规则:
主表必须已经存在数据库中,或者是当前正在创建的表。如果是后一种情况,则主表与从表是同一个表,这样的表称为自参表(顾名思义,就是自我参照的意思),这种结构称为自参完整性。
MySQL支持外键的存储引擎只有InnoDB,这里和主键有区别,注意区分。在创建外键的时候,要求主表必须有对应的索引。从表在创建外键的时候也会自动创建对应的索引。
必须为主表定义主键。
主键不能包含空值,但是允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。
在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键(唯一键、复合主键)。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…] REFERENCES <主表名> 主键列1 [,主键列2,…]
其中:外键名为定义的外键约束的名称,一个表中不能有相同名称的外键;字段名表示子表需要添加外健约束的字段列;主表名即被子表外键所依赖的表的名称;主键列表示主表中定义的主键列或者列组合。
drop table 成绩; drop table 学生; drop table 科目; CREATE TABLE IF NOT EXISTS 学生 ( id int PRIMARY KEY, name varchar(10), ); CREATE TABLE IF NOT EXISTS 科目 ( id int PRIMARY KEY, name varchar(10) ); CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY, 学生ID INT(10), 科目ID INT(10), 成绩 DECIMAL(10,2), CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id), CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id) ); show indexes from 成绩;
提示:关联指的是关系数据库中,相关表之间的联系。它是通过相同的属性或属性组来表示的。子表的外键必须关联父表的主键,且关联字段的数据类型必须匹配,如果类型不一样,则创建子表时会出现错误
ERROR 3780 (HY000): Referencing column '学生ID' and referenced column 'id' in foreign key constraint 's' are incompatible.
建立外键以后产生的影响
在向从表中插入数据时:
insert into 成绩 values(1, null, null, 12.3); insert into 成绩(id, 成绩) values (2, 12.3); insert into 成绩 values(3, 2, 3, 12.3); Cannot add or update a child row: a foreign key constraint fails (`test`.`成绩`, CONSTRAINT `fk_c_s` FOREIGN KEY (`学生ID`) REFERENCES `学生` (`id`)) insert into 学生 values(2, 'aaa'); insert into 科目 values(3, 'bbb');
如果外键字段插入的值,在不为null时,必须在关联的主表字段中存在,才能插入成功,否则报错。
在向从表中更新数据时:
update 成绩 set 学生ID=3 where id=3; Cannot add or update a child row: a foreign key constraint fails (`test`.`成绩`, CONSTRAINT `fk_c_s` FOREIGN KEY (`学生ID`) REFERENCES `学生` (`id`)) update 成绩 set 学生ID=null where id=3;
如果外键字段更新后的值,在不为null时,必须在关联的主表字段中存在时,才能更新成功,否则报错。
在向主表中更新存在已关联从表的字段数据时:
update 学生 set id=3 where id=2; delete from 学生 where id=2; Cannot delete or update a parent row: a foreign key constraint fails (`test`.`成绩`, CONSTRAINT `fk_c_s` FOREIGN KEY (`学生ID`) REFERENCES `学生` (`id`))
无法更新主表中,存在已关联到从表的字段数据,只能更无关联的数据。
在主表删除存在已关联从表的字段数据时:
delete from 学生 where id=2; Cannot delete or update a parent row: a foreign key constraint fails (`test`.`成绩`, CONSTRAINT `fk_c_s` FOREIGN KEY (`学生ID`) REFERENCES `学生` (`id`))
如果存在关联到将被删除的主表记录,的从表记录时,无法删除。必须先删除从表中对应记录后,才能删除主表记录。
在删除从表记录时:
无影响
在删除主表时:
drop table 学生; Cannot drop table '学生' referenced by a foreign key constraint 'fk_c_s' on table '成绩'.
当外键关系还存在时,不能删除主表。必须先删除外键后,才能删除主表。
在删除从表时:无影响
在修改表时添加外键约束
在修改数据表时添加外键约束的语法规则为:
ALTER TABLE <数据表名> ADD CONSTRAINT <索引名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<
我们还是来看看学生成绩表的例子。
drop table 成绩; drop table 学生; drop table 科目; CREATE TABLE IF NOT EXISTS 学生 ( id int PRIMARY KEY, name varchar(10) ); CREATE TABLE IF NOT EXISTS 科目 ( id int PRIMARY KEY, name varchar(10) ); CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY, 学生ID INT(10), 科目ID INT(10), 成绩 DECIMAL(10,2) ); alter table 成绩 add constraint 成绩_学生ID_fk0 foreign key (学生ID) references 学生 (id); alter table 成绩 add constraint 成绩_科目ID_fk0 foreign key (科目ID) references 科目 (id);
删除外键约束
对于数据库中定义的外键,如果不再需要,可以将其删除。外键一旦删除,就会解除主表和从表间的关联关系,MySQL 中删除外键的语法格式如下:
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
去除外键约束后,对应创建在从表上的索引,还继续存在,除非直接drop相关索引。
级联操作
在创建外键的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括RESTRICT、NO ACTION、SET NULL和CASCADE。
其中RESTRICT和NO ACTION相同,是指在子表有关联记录的情况下父表不能更新;CASCADE表示父表在更新或者删除时,更新或者删除子表对应记录;SET NULL则是表示父表在更新或者删除的时候,子表的对应字段被SET NULL。
不允许级联操作
CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY, 学生ID INT(10), 科目ID INT(10), 成绩 DECIMAL(10,2), CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id) ); --或者如下 CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY, 学生ID INT(10), 科目ID INT(10), 成绩 DECIMAL(10,2), CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id) );
进行no action设置之后,与未设置级联操作一致(no action实际是默认模式),删除和更新会报错。
级联更新
CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY, 学生ID INT(10), 科目ID INT(10), 成绩 DECIMAL(10,2), CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON UPDATE CASCADE, CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id) );
进行了on update cascade设置之后,update操作主表,将主表数据和从表中关联的数据字段都一并更新。
update 学生 set id=3 where id=2; select * from 学生; select * from 成绩;
级联删除
CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY, 学生ID INT(10), 科目ID INT(10), 成绩 DECIMAL(10,2), CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON delete CASCADE, CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id) );
进行了on delete cascade设置之后,delete操作主表,将主表数据和从表中关联的数据都一并删除。
delete from 学生 where id=2; select * from 学生; select * from 成绩;
on update cascade 与 on delete cascade 可以连起来写。
级联设置为null
CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY, 学生ID INT(10), 科目ID INT(10), 成绩 DECIMAL(10,2), CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id) ON delete set null, CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id) );
进行了on delete set null 或 on update set null 设置之后,delete 或 update 操作主表,将主表数据和从表中关联的数据字段都一并设为null。
delete from 学生 where id=2; select * from 学生; select * from 成绩;