基本介绍
约束概念
约束实际上就是表中数据的限制条件,用来保证表中数据的准确性和可靠性。比如注册邮箱不能重复,这就需要添加约束。
约束种类
- 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)