MySQL 约束类型

# MySQL 约束类型
约束实际上就是表中数据的限制条件，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。比如name字段中要让其用户名不重复，这就需要添加约束。或者必须注册的时候需要添加邮箱等。
MYSQL中，常用的几种约束：

约束类型：	主键	外键	唯一	非空	自增	默认值
关键字：	primary key	foreign key	unique	not null	auto_increment	default

1、主键约束 primary key

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
但是主键和非空和唯一索引有本质不同。
主键约束除了可以做到”not null unique”之外，还会默认添加”索引——index”
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值。
一张表应该有主键字段，如果没有，表示该表无效
主键值：是当前行数据的唯一标识、是当前行数据的身份证号
即使表中两行记录相关数据相同，但由于主键值不同，所以也认为是两行不同的记录

-- 基本模式
create table temp( 
id int primary key,
name varchar(20)
);
-- 组合模式
create table temp(
id int ,
name varchar(20),
pwd varchar(20),
primary key(id, name)
);
-- 删除主键约束
alter table temp drop primary key;
-- 添加主键约束
alter table temp add primary key(id,name);
-- 修改主键约束
alter  table temp modify id int primary key；

按主键约束的字段数量分类
单一主键：给一个字段添加主键约束
复合主键：给多个字段联合添加一个主键约束(只能用表级定义)
单一主键(列级定义)

mysql> create table t_user(
    -> id int(10) primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.07 sec)

单一主键(表级定义)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> constraint t_user_id_pk primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

复合主键(表级定义)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique,
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.05 sec)

在MySQL数据库提供了一个自增的数字，专门用来自动生成主键值，主键值不用用户维护，自动生成，自增数从1开始，以1递增(auto_increment)

mysql> create table t_user(
    -> id int(10) primary key auto_increment,
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

插入两行记录，id主键值会自动增加

mysql> insert into t_user(name) values('jay');
Query OK, 1 row affected (0.04 sec)
mysql> insert into t_user(name) values('man');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
+----+------+
2 rows in set (0.00 sec)

2、外键约束 foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

-- 基本模式
-- 主表
create table temp(
id int primary key,
name varchar(20)
);
-- 副表
create table temp2(
id int,
name varchar(20),
classes_id int,
foreign key(id) references temp(id)
);
-- 多列外键组合，必须用表级别约束语法
-- 主表
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);
-- 副表
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/*表级别联合外键*/
foreign key(classes_name, classes_number) references classes(name, number) 
);
-- 删除外键约束
alter table student drop foreign key student_id;
-- 增加外键约束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);

什么是外键
若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。
A为基本表，B为信息表
1、外键涉及到的术语
外键约束
外键字段
外键值
2、外键约束、外键字段、外键值之间的关系
某个字段添加外键约束之后，该字段称为外键字段，外键字段中每个数据都是外键值
3、按外键约束的字段数量分类
单一外键：给一个字段添加外键约束
复合外键：给多个字段联合添加一个外键约束
4、一张表可以有多个外键字段（与主键不同）
5、分析场景
设计数据库表，用来存储学生和班级信息
两种方案
方案一：将学生信息和班级信息存储到一张表

sno     sname      classno      cname
1       jay         100         浙江省第一中学高三1班
2       lucy        100         浙江省第一中学高三1班
3       king        200         浙江省第一中学高三2班

缺点：数据冗余，比如cname字段的数据重复太多
方案二：将学生信息和班级信息分开两张表存储
学生表（添加单一外键）

sno(pk)     sname       classno(fk)
1           jack        100
2           lucy        100
3           king        200

班级表

cno(pk)     cname
100         浙江省第一中学高三1班
200         浙江省第一中学高三2班

结论

为了保证学生表中的classno字段中的数据必须来自于班级表中的cno字段中的数据，有必要给学生表中的classno字段添加外键约束

注意点

• 外键值可以为null
• 外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束
• 有了外键引用之后，表分为父表和子表
• 班级表：父表
• 学生表：子表
• 创建先创建父表
• 删除先删除子表数据
• 插入先插入父表数据
  存储学生班级信息

mysql> drop table if exists t_student;
mysql> drop table if exists t_class;
mysql> create table t_class(
    -> cno int(10) primary key,
    -> cname varchar(128) not null unique
    -> );
mysql> create table t_student(
    -> sno int(10) primary key auto_increment,
    -> sname varchar(32) not null,
    -> classno int(3),
    -> foreign key(classno) references t_class(cno)
    -> );
mysql> insert into t_class(cno,cname) values(100,'aaaaaaxxxxxx');
mysql> insert into t_class(cno,cname) values(200,'oooooopppppp');
mysql> insert into t_student(sname,classno) values('jack',100);
mysql> insert into t_student(sname,classno) values('lucy',100);
mysql> insert into t_student(sname,classno) values('king',200);

班级表t_class

mysql> select * from t_class;
+-----+--------------+
| cno | cname        |
+-----+--------------+
| 100 | aaaaaaxxxxxx |
| 200 | oooooopppppp |
+-----+--------------+

学生表t_student

mysql> select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | jack  |     100 |
|   2 | lucy  |     100 |
|   3 | king  |     200 |
+-----+-------+---------+

上表中找出每个学生的班级名称

mysql> select s.*,c.* from t_student s join t_class c on s.classno=c.cno;
+-----+-------+---------+-----+--------------+
| sno | sname | classno | cno | cname        |
+-----+-------+---------+-----+--------------+
|   1 | jack  |     100 | 100 | aaaaaaxxxxxx |
|   2 | lucy  |     100 | 100 | aaaaaaxxxxxx |
|   3 | king  |     200 | 200 | oooooopppppp |
+-----+-------+---------+-----+--------------+

3、 唯一约束unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
唯一约束不允许出现重复的值，但是可以为多个null。
同一个表可以有多个唯一约束，多个列组合的约束。
在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

-- 创建表时设置，表示用户名、密码不能重复
    create table temp(
    id int not null ,
    name varchar(20),
    password varchar(10),
    unique(name,password)
);
-- 添加唯一约束
alter table temp add unique (name, password);
-- 修改唯一约束
alter table temp modify name varchar(25) unique;
-- 删除约束
alter table temp drop index name;

创建表，保证邮箱地址唯一(列级约束)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique
    -> );
Query OK, 0 rows affected (0.03 sec)

表级约束

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128），
    -> unique(email)
    -> );

如果插入相同email会报错

mysql> insert into t_user(id,name,email) values(1,'xlj','932834897@qq.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user(id,name,email) values(2,'jay','932834897@qq.com');
ERROR 1062 (23000): Duplicate entry '932834897@qq.com' for key 'email'

使用表级约束，给多个字段联合约束

联合约束，表示两个或以上的字段同时与另一条记录相等，则报错

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128),
    -> unique(name,email)
    -> );
Query OK, 0 rows affected (0.01 sec)

插入第一条数据

mysql> insert into t_user(id,name,email) values(1,'xxx','qq.com');
Query OK, 1 row affected (0.05 sec)

插入第二条数据如果是与联合字段中的一条相同另一条相同，也是可以的

mysql> insert into t_user(id,name,email) values(2,'mmm','qq.com');
Query OK, 1 row affected (0.05 sec)

插入第三条数据，如果与联合字段都相同，则报错

mysql> insert into t_user(id,name,email) values(3,'mmm','qq.com');
ERROR 1062 (23000): Duplicate entry 'mmm-qq.com' for key 'name'

3、表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128),
    -> constraint t_user_email_unique unique(email)
    -> );
Query OK, 0 rows affected (0.06 sec)

constraint是约束关键字，t_user_email_unique自己取的名字

例：用户名既不能为空，也不能重复

name varchar(32) not null unique

4、非空约束 not null 与 默认值 default

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
用not null约束的字段不能为null值，必须给定具体的数据
Null类型特征：
　　所有的类型的值都可以是null，包括int、float 等数据类型

-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table temp(
           id int not null,
           name varchar(255) not null default  'abc',
           sex char null
)；
-- 增加非空约束
alter table temp
modify sex varchar(2) not null;
-- 取消非空约束
alter table temp modify sex varchar(2) null;
-- 取消非空约束，增加默认值
alter table temp modify sex varchar(2) default 'abc' null;

创建表，给字段添加非空约束（创建用户表，用户名不能为空）

    mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.08 sec)

如果没有插入name字段数据，则会报错

mysql> insert into t_user (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value