(二)表结构
- 表1 – depart:
表:depart | |
---|---|
id | title |
1 | 开发 |
2 | 运维 |
3 | 销售 |
- 表2 – info:
表:info | ||||
---|---|---|---|---|
id | name | age | depart_id | |
1 | 名1 | name1@gmail.com | 10 | 1 |
2 | name2 | name2@qq.com | 20 | 1 |
3 | 名3 | name3@gmail.com | 30 | 2 |
4 | nam4 | name4@163.com | 22 | 1 |
5 | joy | joy@gmail.com | 18 | 3 |
6 | joywon | joywon@gmail.com | 22 | 1 |
7 | won | won@gmail.com | 30 | 1 |
1、一对多(约束关系)
-
需要添加 外键(外键约束),可以在创建数据表过程中添加一个 外键约束,保证某一列的值必须数其他表中的特定列已经存在的值,例如:
info.depart_id
的值必须是depart.id
中已经存在的值 -
示例:
create table depart( id int not null primary key auto_increment, title varchar(16) not null )default charset=utf8; create table info( id int not null primary key auto_increment, name varchar(16) not null, email varchar(32) not null, age int, depart_id int not null, -- 约束 起个名字(fk_当前表名_关联表名) 外键 当前表(约束列) 参考 关联表(约束列) constraint fk_info_depart foreign key info(depart_id) references depart(id) )default charset=utf8;
-
当前数据表我们已经在创建完成了,所以需要额外添加外键,因为是修改列的数据(所以用alter):
alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id); -- 当添加外键约束时,999显然不在depart表中,所以会报错 mysql> insert into info(name,email,age,depart_id) values("xx","xxx",20,999); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_sql`.`info`, CONSTRAINT `fk_info_depart` FOREIGN KEY (`depart_id`) REFERENCES `depart` (`id`))
-
删除外键:
alter table info drop foreign key fk_info_depart; -- 当删除外键时,999就成功插入数据行中 mysql> alter table info drop foreign key fk_info_depart; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into info(name,email,age,depart_id) values("xx","xxx",20,999); Query OK, 1 row affected (0.00 sec)
2、多对多(约束关系)
-
新建两个数据表(boy 和 girl)
create table boy( id int not null primary key auto_increment, name varchar(16) not null )default charset=utf8;
create table gril( id int not null primary key auto_increment, name varchar(16) not null )default charset=utf8;
-
创建两者的关系表(bg)
create table bg( id int not null auto_increment primary key, boy_id int not null, gril_id int not null, constraint fk_bg_boy foreign key bg(boy_id) references boy(id), constraint fk_bg_gril foreign key bg(gril_id) references gril(id) )default charset=utf8;
-
删除外键
alter table info drop foreign key fk_bg_boy; alter table ifo drop foreign key fk_bg_gril;