mysql 字段约束

基本介绍

约束概念

约束实际上就是表中数据的限制条件，用来保证表中数据的准确性和可靠性。比如注册邮箱不能重复，这就需要添加约束。

约束种类

• not null：非空，用于保证该字段的值不为空值
• default：默认，用于设置该字段的默认值
• primary key：主键，用于保证该字段值具有唯一性，并且非空，
• unique：唯一，用于保证该字段的值具有唯一性，且字段可以为空，但是只能有一个值为空。
• check：检查约束，mysql中语法支持，但不生效
• foreign key：外键，用于限制两个表的关系，保证该字段的值来自主表的关联列。在从表中添加外键约束，用于从主表中引用某列的值。

列级约束

create table if not exists t_stuinfo(
2     id int primary key,    #主键
3     stuName varchar(20) not null,    #非空
4     gender char(1) check(gender='男' or gender='女'),    #检查约束，MySql没有效果但不报错
5     seat int unique,    #唯一约束
6     age int default 18,    #默认（值）约束
7     majorId int references major(id) #外键约束，MySql没有效果，但不报错
8     );

主键 primary key

基本概念

• 主键是当前行数据的唯一标识，定义主键约束后，该列不能重复且不能为空
• 主键是一种索引，索引的作用是加速查询效率。因此主键约束能加速我们查询的效率
• 主键对于innodb引擎来说是必须要的，没有不行。即使我们在创建innodb引擎的表，没有创建主键，系统也会自动创建一个隐藏的主键

主键涉及术语

主键约束，主键字段，主键值，表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值

指定主键的两种方式

//方式一
mysql> create table test1(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.07 sec)

mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

//方式二
mysql> create table test2(id int,name varchar(30),primary key(id));
Query OK, 0 rows affected (0.06 sec)

mysql> desc test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

primary key约束的字段不能重复且不能为空

mysql> create table test1(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.07 sec)

mysql> insert test1 values(100,'aaa');
Query OK, 1 row affected (0.03 sec)

//主键不能重复
mysql> insert test1 values(100,'bbb');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
//主键不能为null
mysql> insert test1 values(null,'bbb');
ERROR 1048 (23000): Column 'id' cannot be null  

主键是唯一的，主键分为单一主键和复合主键

//报错： 定义了多个主键
mysql> create table test3(id int primary key,name varchar(30) primary key,email varchar(60));
ERROR 1068 (42000): Multiple primary key defined

//单一主键
mysql> create table test3(id int primary key,name varchar(30),email varchar(60));
Query OK, 0 rows affected (0.07 sec)

mysql> desc test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| email | varchar(60) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

//复合主键
mysql> create table test4(id int,name varchar(30),email varchar(60),primary key(id,name));
Query OK, 0 rows affected (0.06 sec)
//为什么有两个主键？  因为是复合主键 id 和 name 合起来当作一个主键
mysql> desc test4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(30) | NO   | PRI |         |       |
| email | varchar(60) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into test4 values(100,'aaa','aaa@qq.com');
Query OK, 1 row affected (0.04 sec)

mysql> insert into test4 values(100,'bbb','bbb@qq.com');
Query OK, 1 row affected (0.04 sec)
//复合主键列的值同时相同时 才会触发主键约束
mysql> insert into test4 values(100,'aaa','aaa@qq.com');
ERROR 1062 (23000): Duplicate entry '100-aaa' for key 'PRIMARY'

主键约束与"not null unique"区别

给某个字段添加主键约束之后，该字段不能重复也不能为空，效果和"not null unique"约束相同，但是本质不同。

主键约束除了可以做到"not null unique"之外，还会默认添加”索引——index”

自增约束 auto_increment

自增约束不属于这六大约束范围，但它经常配合主键使用，所以写在这里

基本概念

如果希望某个字段的值，自动的增长，由系统维护，我们可以将该列设置为auto_incrment，即自增长，自增长可以保证产生唯一的值

入门案例

//设置主键自增长
mysql> create table test1 (id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.08 sec)

//第一种加法
//当字段是auto_increment时，如果设置为null，系统会自动根据auto_increment的原则来添加，而不是真的添加null
mysql> insert into test1 values(null,'zz');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test1 values(null,'xx');
Query OK, 1 row affected (0.03 sec)

//第二种加法
mysql> insert into test1 (name) values('cc');
Query OK, 1 row affected (0.03 sec)

//可以直接给auto_increment 指定值
mysql> insert into test1 values(10,'vv');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test1 (name) values('bb');
Query OK, 1 row affected (0.02 sec)

//自增长会找该字段的最大值 +1
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
|  1 | zz   |
|  2 | xx   |
|  3 | cc   |
| 10 | vv   |
| 11 | bb   |
+----+------+

使用细节

• auto_increment的默认起始值是1，每条新记录递增1，可以通过 alter table 来修改 auto_increment的默认起始值
• 一般来说自增长是和primary key 配合使用的，自增长也可以单独使用，但是需要配合一个unique
• 自增长修饰的字段为整数型的，虽然小数也可以但是非常非常少这样使用
• 自增长如果不给则会找该字段的最大值 +1，也可以指定值，该值不能重复

//auto_increment 配合 unique 使用
mysql> create table test2 (id int unsigned unique auto_increment,name varchar(20));
Query OK, 0 rows affected (0.06 sec)

//修改auto_increment的默认起始值是100
mysql> alter table test2 auto_increment=100;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

//自增长如果不给则会找该字段的最大值 +1，也可以指定值，该值不能重复
mysql> insert into test2 values(null,'zz');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test2 values(1,'kk');
Query OK, 1 row affected (0.03 sec)

//自增长如果不给则会找该字段的最大值 +1，也可以指定值，该值不能重复
mysql> insert into test2 values(200,'xx');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test2;
+-----+------+
| id  | name |
+-----+------+
|   1 | kk   |
| 100 | zz   |
| 200 | xx   |
+-----+------+
3 rows in set (0.00 sec)

mysql> insert into test2 values(null,'pp');
Query OK, 1 row affected (0.04 sec)

mysql> select * from test2;
+-----+------+
| id  | name |
+-----+------+
|   1 | kk   |
| 100 | zz   |
| 200 | xx   |
| 201 | pp   |
+-----+------+
4 rows in set (0.00 sec)

非空 not null

非空约束，表示这个字段的值不能为空，例如：账户名、密码等

mysql> create table test5(id int primary key,name varchar(30) not null);
Query OK, 0 rows affected (0.10 sec)

mysql> desc test5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> insert into test5 values(100,null);
ERROR 1048 (23000): Column 'name' cannot be null

唯一 unique

unique 唯一性约束，表示这个字段的值不能重复

mysql> create table test6(id int primary key,name varchar(30) not null default '',email varchar(60) unique);
Query OK, 0 rows affected (0.67 sec)

mysql> desc test6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | NO   |     |         |       |
| email | varchar(60) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> insert into test6 values(1,'aaa','aaa@qq.com');
Query OK, 1 row affected (0.04 sec)

mysql> insert into test6 values(2,'bbb','aaa@qq.com');
ERROR 1062 (23000): Duplicate entry 'aaa@qq.com' for key 'email'

细节说明

如果指定 unique 且指定 not null，则该字段约束效果类似 primary key

如果指定 unique 但没有指定 not null，则 unique 字段可以为null， unique 不认为 null 重复，所以可以有多个null

mysql> create table test6(id int primary key,name varchar(30) not null default '',email varchar(60) unique);
Query OK, 0 rows affected (0.67 sec)

mysql> desc test6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | NO   |     |         |       |
| email | varchar(60) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> insert into test6 values(3,'ccc',null);
Query OK, 1 row affected (0.03 sec)

mysql> insert into test6 values(4,'ddd',null);
Query OK, 1 row affected (0.03 sec)

mysql>  select * from test6;
+----+------+------------+
| id | name | email      |
+----+------+------------+
|  1 | aaa  | aaa@qq.com |
|  3 | ccc  | NULL       |
|  4 | ddd  | NULL       |
+----+------+------------+

一张表可以有多个 unique 约束的字段，而 primary key 只能约束一个字段

mysql> create table test7(id int primary key,name varchar(30) not null default '',email varchar(60) unique,card_id int not null unique);
Query OK, 0 rows affected (0.06 sec)

mysql> desc test7;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | NO   |     |         |       |
| email   | varchar(60) | YES  | UNI | NULL    |       |
| card_id | int(11)     | NO   | UNI | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

默认值 default

创建列时可以显式指定列默认值，当插入数据时如果未主动设置，则自动添加默认值

使用细节

• primary key 不要和 default 同用，因为第二次为主键加默认值就会因主键重复而报错
• 约束not null 不能和 显式约束 default null 共存，但可以和隐式约束 default null 共存
• mysql数据库有一个隐式的默认值，如果一个数据列可为null，那么null就是该列的默认值

mysql> create table test8(id int primary key,name varchar(30),age int default 18,email varchar(60) not null);
Query OK, 0 rows affected (0.07 sec)

mysql> desc test8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |  隐式 Default=null
| name  | varchar(30) | YES  |     | NULL    |       |  隐式 Default=null
| age   | int(11)     | YES  |     | 18      |       |  显式 Default=18
| email | varchar(60) | NO   |     | NULL    |       |  隐式 Default=null
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> insert into test8 (id,email) values(1,'aaa@qq.com');
Query OK, 1 row affected (0.04 sec)

//虽然 email 隐式 Default=null 但是该字段设置了not null 综合效果是 该字段无默认值 且不能为null
mysql> insert into test8 (id) values(2);
ERROR 1364 (HY000): Field 'email' doesn't have a default value

mysql> select * from test8;
+----+------+------+------------+
| id | name | age  | email      |
+----+------+------+------------+
|  1 | NULL |   18 | aaa@qq.com |
+----+------+------+------------+
1 row in set (0.00 sec)

外键约束

创建主表班级表

mysql> create table my_class (id int primary key,name varchar(20) not null default '');
Query OK, 0 rows affected (0.08 sec)

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> insert into my_class values(10,'php大神班');
Query OK, 1 row affected (0.45 sec)

mysql> insert into my_class values(20,'java大神班');
Query OK, 1 row affected (0.04 sec)

mysql> select * from my_class;
+----+------------+
| id | name       |
+----+------------+
| 10 | php大神班  |
| 20 | java大神班 |
+----+------------+
2 rows in set (0.00 sec)

创建从表学生表

//重点 foreign key(class_id) references my_class(id))

# class_id 表示学生表的外键字段
# my_class 表示要关联的哪个表,这里指班级表
# my_class(id)  表示要关联my_class表中的id字段
# references  引用的意思

mysql> create table students (id int primary key,name varchar(20) not null default '',class_id int,foreign key(class_id) references my_class(id));
Query OK, 0 rows affected (0.10 sec)

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   |     |         |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql> create table students (id int primary key,name varchar(20) not null default '',class_id int,foreign key(class_id) references my_class(id));
Query OK, 0 rows affected (0.10 sec)

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   |     |         |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

向学生表插入数据（从表）

mysql> insert into students values(100,'张三',10);
Query OK, 1 row affected (0.03 sec)

mysql> insert into students values(101,'李四',20);
Query OK, 1 row affected (0.04 sec)

//30号班级在 my_class 表中不存在，因此添加失败
mysql> insert into students values(102,'王五',30);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`))

//null不违反外键约束，是否可以设置为 null 取决于 class_id 字段是否设置 not null
mysql> insert into students values(102,'王五',null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from students;
+-----+------+----------+
| id  | name | class_id |
+-----+------+----------+
| 100 | 张三 |       10 |
| 101 | 李四 |       20 |
| 102 | 王五 |     NULL |
+-----+------+----------+
3 rows in set (0.00 sec)

使用细节

• 在从表中设置外键关系，主表无需其他操作
• 从表的外键列的值必须在主表的关联列中出现过，或者为 null（是否可以为 null 取决于外键是否设置了 not null）
• 从表的外键列指向的主表的关联列字段要求是 primary key 或者是 unique 约束
• 主表和从表需要是 innodb 引擎，并且禁止使用临时表，这样才支持外键
• 从表的外键列的类型和主表的关联列的类型要求一致或兼容，长度可以不一样，名称无要求
• 插入数据时，先插入主表，再插入从表
• 删除数据时，先删除从表数据，主表的关联列不在有从表数据指向时才能删除主表的该行数据

mysql> select * from my_class;
+----+------------+
| id | name       |
+----+------------+
| 10 | php大神班  |
| 20 | java大神班 |
+----+------------+
2 rows in set (0.00 sec)

mysql> select * from students;
+-----+------+----------+
| id  | name | class_id |
+-----+------+----------+
| 100 | 张三 |       10 |
| 101 | 李四 |       20 |
| 102 | 王五 |     NULL |
+-----+------+----------+
3 rows in set (0.00 sec)

//删除表  核心思想就是不能影响别的表

//直接删除 10号班级  班里的学生咋办？ 显然不合理 失败
mysql> delete from my_class where id=10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `my_class` (`id`))

//先删除 10号班级的所有学生  再删除 10 号班级  成功
mysql> delete from students where class_id=10;
Query OK, 1 row affected (0.03 sec)

mysql> delete from my_class where id=10;
Query OK, 1 row affected (0.03 sec)

check

基本介绍

check 约束用于限制列中的值的范围，假如在check列上定义了check约束，并要求sal值在100-1000之间，如果不在就报错

oracle 和 sql server 均支持 check，但mysql目前不支持，只做语法校验，但不会生效

代码示例

mysql> create table test9(
    -> id int primary key,
    -> name varchar(20) not null default '',
    -> sex enum('man','woman'),
    -> sal float check(sal>100 and sal<1000)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test9 values(1,'张三','man','80');
Query OK, 1 row affected (0.03 sec)

mysql> desc test9;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | NO   | PRI | NULL    |       |
| name  | varchar(20)         | NO   |     |         |       |
| sex   | enum('man','woman') | YES  |     | NULL    |       |
| sal   | float               | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

//创建时 check(sal>100 and sal<1000) 语法通过，但查看表结构发现该约束并未生效
mysql> show create table test9;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test9 | CREATE TABLE `test9` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL DEFAULT '',
  `sex` enum('man','woman') DEFAULT NULL,
  `sal` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

综合练习

现有一个商店的数据库三个表组成

商品goods（商品号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider)
客户customer（客户号customer_id,姓名name,住址address,电邮email性别sex,身份证card_Id);
购买purchase（购买订单号order_id，客户号customer_id,商品号goods_id,购买数量nums);

建表，在定义中要求声明 [进行合理设计]：

每个表的主外键；
客户的姓名不能为空值；
电邮不能够重复;
客户的性别[男|女]
单价unitprice 在 1.0 - 9999.99 之间 check

商品goods

mysql> create table goods(
    -> goods_id int unsigned primary key,
    -> goods_name varchar(100) not null default '',
    -> unitprice decimal(8,2) not null default 0.00,
    -> category smallint unsigned not null default 0 comment '商品类别',
    -> provider varchar(50) not null default '' comment '供应商'
    -> )charset=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> desc goods;
+------------+----------------------+------+-----+---------+-------+
| Field      | Type                 | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| goods_id   | int(10) unsigned     | NO   | PRI | NULL    |       |
| goods_name | varchar(100)         | NO   |     |         |       |
| unitprice  | decimal(8,2)         | NO   |     | 0.00    |       |
| category   | smallint(5) unsigned | NO   |     | 0       |       |
| provider   | varchar(50)          | NO   |     |         |       |
+------------+----------------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

客户customer

mysql> create table customer(
    -> customer_id int unsigned primary key,
    -> name varchar(30) not null default '',
    -> address varchar(80) not null default '',
    -> email varchar(50) not null unique,
    -> sex enum('男','女') not null,
    -> card_id varchar(30) not null unique
    -> )charset=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> desc customer;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| customer_id | int(10) unsigned | NO   | PRI | NULL    |       |
| name        | varchar(30)      | NO   |     |         |       |
| address     | varchar(80)      | NO   |     |         |       |
| email       | varchar(50)      | NO   | UNI | NULL    |       |
| sex         | enum('男','女')  | NO   |     | NULL    |       |
| card_id     | varchar(30)      | NO   | UNI | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

购买purchase

mysql> create table purchase(
    -> order_id varchar(30) primary key comment '订单号',
    -> customer_id int unsigned comment '客户号',
    -> goods_id int unsigned,
    -> nums smallint unsigned not null default 0,
    -> foreign key (customer_id) references customer(customer_id),
    -> foreign key (goods_id) references goods(goods_id)
    -> )charset=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> desc purchase;
+-------------+----------------------+------+-----+---------+-------+
| Field       | Type                 | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| order_id    | varchar(30)          | NO   | PRI | NULL    |       |
| customer_id | int(10) unsigned     | YES  | MUL | NULL    |       |
| goods_id    | int(10) unsigned     | YES  | MUL | NULL    |       |
| nums        | smallint(5) unsigned | NO   |     | 0       |       |
+-------------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)