约束条件之主键
关键字：primary key
-
定义
主键（PRIMARY KEY）的完整称呼是“主键约束”。MySQL主键约束是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。
-
强调
在关系数据库，一个表中，只能有一个主键（primary key），有些数据库没有主键，系统报错。在MySQL数据库中，建立表时，可以有主键，也可以没有（推荐建立表时必须要有主键）。 -
单从约束角度上而言主键等价于非空且唯一 not null unique
mysql> create table t1(
-> id int primary key,
-> name varchar(32) default 'lzq'
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1(name) values('jason');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into t1(id, name) values(1, 'jason');
Query OK, 1 row affected (0.05 sec)
mysql> insert into t1(id, name) values(1, 'jason');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t1(id, name) values(2, 'kevin');
Query OK, 1 row affected (0.05 sec)
-
InnoDB存储引擎规定一张表必须有且只有一个主键
-
如果既没有一个主键，也没有not null + unique的情况下，那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(类似于书的目录) -
如果创建表的时候没有主键，但是有非空加唯一，那么InnoDB存储引擎会自动将该字段设置为主键(隐藏就意味着而无法使用 即无法加快数据查询) 如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个)
create table t2(
tid int not null unique,
pid int not null unique,
cid int not null unique
);
-
创建表的时候都应该有一个id字段 并且该字段应该作为主键 create table t3(
id int primary key,
name varchar(32)
);
-
联合主键(多个字段组合 本质还是一个主键) create table t4(
id int,
name varchar(32),
pwd int,
primary key(id, pwd)
);
-
自增 关键字：auto_increment 该约束条件不能单独使用 必须跟在键后面(主要配合主键一起使用) mysql> create table t2(
-> id int primary key auto_increment,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t2(name) values('jason');
Query OK, 1 row affected (0.05 sec)
mysql> insert into t2(name) values('kevin');
Query OK, 1 row affected (0.05 sec)
mysql> select * from t2;
+
| id | name |
+
| 1 | jason |
| 2 | kevin |
+
2 rows in set (0.00 sec)
特点：
-
自增的操作不会因为执行删除数据的操作而回退或者重置 mysql> delete from t2 where id=2;
Query OK, 1 row affected (0.05 sec)
mysql> insert into t2(name) values('oscar');
Query OK, 1 row affected (0.05 sec)
mysql> select * from t2;
+
| id | name |
+
| 1 | jason |
| 3 | oscar |
+
2 rows in set (0.00 sec)
-
如果非要重置主键 需要格式化表 truncate 表名; # 删除表数据并重置主键值
mysql> truncate t2;
Query OK, 0 rows affected (0.08 sec)
mysql> select * from t2;
Empty set (0.00 sec)
优点： （1）数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利；
（2）数字型，占用空间小，易排序，在程序中传递也方便；
（3）如果通过非系统增加记录时，可以不用指定该字段，不用担心主键重复问题。
约束条件之外键
外键约束的创建
-
创建外键约束的条件 MySQL数据库外键的创建，需要满足以下四个条件，否则会被MySQL数据库拒绝：
- 创建外键的表和列存在
- 组成外键的列存在索引
- 必须指定数据表的引擎为InnoDB
- 外键字段和关联字段，数据类型必须一致
-
外键关系 外键是用来记录表与表之间的数据关系，关系一共有四种
- 一对一
- 一对多
- 多对多
- 没有关系
-
在创建数据表时创建外键约束 在创建数据表时创建外键约束，只需要在创建数据表的create语句后面，使用foreign key关键字指定本表的外键字段，使用reference关键字指定关联表的关联字段，并且明确约束行为即可。
创建外键约束的SQL语句示例如下： create table course(
id int primary key auto_increment,
name varchar(32),
stu_id int,
foreign key(stu_id) references student(id)
on update cascade
on delete cascade
);
create table student(
id int primary key auto_increment,
name varchar(32)
);
-
foreign key的约束效果 1.创建表的时候 应该先创建被关联表(没有外键字段的表)
2.插入数据的时候 应该先插入被关联表(没有外键字段的表)
外键字段填入的值只能是被关联表中已经存在的值
3.被关联字段无法修改和删除 -
级联更新、级联删除 理解
被关联数据一旦变动 关联的数据会随之同步变动 on update cascade
on delete cascade
扩展:
在实际工作中 很多时候可能并不会使用外键
因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
我们为了能够描述出表数据的关系 又不想使用外键
自己通过写SQL 建立代码层面的关系
表关系之多对多
针对多对多表关系，外键字段不能建在任意一方!!!应该创建第三方表专门用来存放对应关系
create table book(
id int primary key auto_increment,
title varchar(32)
);
create table author(
id int primary key auto_increment,
name varchar(32)
);
create table book2author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
如果不创建第三方表来存放对应关系的话，会出现双方需要的字段都还未创建
表关系之一对一
-
举例说明
以用户表和用户详情表为例说明 1.先站在用户的层面上
问：单个用户可以对应多个用户详情吗？
答：不可以 2.再站在用户详情表的层面上
问：单个用户详情可以对应多个用户吗？
答：不可以 总结：如果两边层面都不可以，那么关系则为一对一或者是没有关系
针对一对一的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询
create table User(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
detail_id int unique,
foreign key(detail_id) references UserDetail(id)
on update cascade
on delete cascade
);
create table UserDetail(
id int primary key auto_increment,
phone bigint,
age int
);
表关系之一对多
-
举例说明
以学生表和部门表为例说明 1.先站在学生的层面上
问：一个学生可以对应多个部门吗？
答：不可以 2.再站在部门的层面上
问：单个部门可以对应多个学生吗？
答：可以 总结：如果一边层面可以，另一边层面不可以，那么关系则为一对多
针对一对多的表关系 外键字段建在多的表里
其实一对一跟一对多的区别就在于一个唯一性的关键字(unique)
create table student(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
dep_id int,
foreign key(dep_id ) references dep(id)
on update cascade
on delete cascade
);
create table dep(
id int primary key auto_increment,
dep_desc varchar(32)
);
今日作业
书籍表与出版社表(多对多)
mysql> create table book(
-> id int primary key auto_increment,
-> book_name varchar(32)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> create table press(
-> id int primary key auto_increment,
-> press_name varchar(32)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> create table book2press(
-> id int primary key auto_increment,
-> book_id int,
-> foreign key(book_id) references book(id)
-> on update cascade
-> on delete cascade,
-> press_id int,
-> foreign key(press_id) references press(id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.04 sec)
学生表与班级表(一对多)
mysql> create table student(
-> stu_id int primary key auto_increment,
-> stu_name varchar(32),
-> class_id int,
-> foreign key(class_id) references class(id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table class(
-> id int primary key auto_increment,
-> class_name varchar(32)
-> );
Query OK, 0 rows affected (0.08 sec)
老师表与课程表(一对一)
mysql> create table course(
-> id int primary key auto_increment,
-> course_name varchar(32)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> create table teacher(
-> tea_id int primary key auto_increment,
-> tea_name varchar(32),
-> course_id int unique,
-> foreign key(course_id) references course(id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.04 sec)
课程表与班级表(多对多)
mysql> create table course1(
-> course_id int primary key auto_increment,
-> course_name varchar(32)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> create table class1(
-> class_id int primary key auto_increment,
-> class_name varchar(32)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> create table course2class(
-> id int primary key auto_increment,
-> cou_id int,
-> foreign key(cou_id) references course1(course_id)
-> on update cascade
-> on delete cascade,
-> cla_id int,
-> foreign key(cla_id) references class1(class_id)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.12 sec)
服务器表与应用程序表(一对多)
mysql> create table server(
-> pid int primary key auto_increment,
-> server_name varchar(32)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> create table client(
-> id int primary key auto_increment,
-> client_name varchar(32),
-> server_pid int,
-> foreign key(server_pid) references server(pid)
-> on update cascade
-> on delete cascade
-> );
Query OK, 0 rows affected (0.11 sec)
|