MySQL数据库约束详解

mysql常见约束有：1、非空约束“not null”；2、唯一性约束“unique”；3、主键约束“primary key”；4、外键约束“foreign key”；5、默认值约束“Default”等等。

基本介绍

约束用于确保数据库的数据满足特定的商业规则

在mysql中，约束包括：not null，unique，primary key，foreign key 和check 5种

1.primary key(主键)

字段名 字段类型 primary key

于唯一地标识表行的数据，当定义主键约束之后，该列不能重复

细节说明

primary key不能重复而且不能为null
一张表最多只能有一个主键，但是可以是复合主键
主键的指定方式有两种
- 直接在字段名后面指定：字段名 primary key
- 在表定义最后写 primary key（列名）
使用desc 表名，可以看到primary key的情况
在实际开发中，每一张表往往都会设计一个主键

例子

-- 主键的使用 -- id name email CREATE TABLE t17( id INT PRIMARY KEY, -- 表示id列是主键 `name` VARCHAR(32), email VARCHAR(32) ); INSERT INTO t17 VALUES(1,'jack','jack@sohu.com'); INSERT INTO t17 VALUES(2,'tom','tom@sohu.com'); -- 1. primary key不能重复而且不能为null INSERT INTO t17 VALUES(NULL,'jack','jack@sohu.com');-- 插入失败，Column 'id' cannot be null -- 2. 一张表最多只能有一个主键，但是可以是复合主键（比如id+name） -- 演示复合主键 CREATE TABLE t18( id INT, `name` VARCHAR(32), email VARCHAR(32), PRIMARY KEY(id,`name`) -- 这里就是复合主键 ); INSERT INTO t18 VALUES(1,'tom','tom@sohu.com'); -- ok INSERT INTO t18 VALUES(1,'jack','jack@sohu.com');-- OK INSERT INTO t18 VALUES(1,'tom','12345@sohu.com'); -- false SELECT * FROM t18; -- 3. 主键的指定方式有两种 -- 3.1直接在字段名后面指定：字段名 primary key CREATE TABLE t19( id INT PRIMARY KEY, `name` VARCHAR(32), email VARCHAR(32) ); -- 3.2在表定义最后写 primary key（列名） CREATE TABLE t20( id INT, `name` VARCHAR(32), email VARCHAR(32), PRIMARY KEY(id) ); -- 4. 使用desc 表名，可以看到primary key的情况 DESC t20; -- 查看t20的情况，显示约束的情况

2.not null(非空)

如果在列上定义了not null，那么当插入数据时，必须为列提供数据

字段名 字段类型 not null

3.unique(唯一)

当定义了唯一约束后，该列值是不能重复的

字段名 字段类型 unique

unique细节

如果没有指定not null，则unique字段可以有多个null
一张表可以有多个unique字段

-- unique的使用 CREATE TABLE t21( id INT UNIQUE, -- 表示id列不可重复 `name` VARCHAR(32), email VARCHAR(32) ) INSERT INTO t21 VALUES(1,'jack','jack@sohu.com'); -- ok INSERT INTO t21 VALUES(1,'tom','tom@sohu.com'); -- false，Duplicate entry '1' for key 'id' -- unique细节 -- 1. 如果没有指定not null，则unique字段可以有多个null -- 如果一个列（字段），是 unique not null 则使用效果类似 primary key INSERT INTO t21 VALUES(NULL,'tom','tom@sohu.com');-- ok SELECT * FROM t21; -- 2. 一张表可以有多个unique字段 CREATE TABLE t22( id INT UNIQUE, -- 表示id列不可重复 `name` VARCHAR(32) UNIQUE, -- 表示name也不可以重复 email VARCHAR(32) ) DESC t22;

4.foreign key(外键)

foreign key(外键)

用于定义主表和从表之间的关系：

外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。

当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

语法

foreign key(本表字段名) references 主表名(主键名或unique字段名)

例子

-- 外键演示 -- 创建 主表 my_class CREATE TABLE my_class( id INT PRIMARY KEY, -- 班级编号 `name` VARCHAR(32) NOT NULL DEFAULT '' ); -- 创建从表 my_stu CREATE TABLE my_stu( id INT PRIMARY KEY,-- 学生编号 `name` VARCHAR(32) NOT NULL DEFAULT '', class_id INT, -- 学生所在的班级的编号 -- 下面指定外键关系 FOREIGN KEY(class_id) REFERENCES my_class(id) ); -- 测试数据 INSERT INTO my_class VALUES(100,'java'),(200,'web'); INSERT INTO my_class VALUES(300,'php'); SELECT * FROM my_class; INSERT INTO my_stu VALUES(1,'tom',100); INSERT INTO my_stu VALUES(2,'jack',200); INSERT INTO my_stu VALUES(3,'hsp',300); INSERT INTO my_stu VALUES(4,'mary',400); -- 失败，因为400号班级不存在 SELECT * FROM my_stu;

外键细节说明

外键指向的主表的字段，要求是primary key 或是unique
表的类型是innodb，这样的表才支持外键
外键字段的类型要和主键字段的类型一致（长度可以不同）
外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
一旦建立主外键的关系，数据就不能随意删除了

5.check

check

用于强制行数据必须满足的条件。假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在此范围，就会提示出错

提示：oracle和sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

语法

列名 类型 check(check条件)

在mysql中实现check的功能，一般是在程序中控制或者通过触发器完成

例子

-- 演示check的使用 -- 目前mysql5.7还不支持check，只做语法校验但不会生效 -- 测试 CREATE TABLE t23( id INT PRIMARY KEY, `name` VARCHAR(32), sex VARCHAR(6) CHECK(sex IN('man','woman')), sal DOUBLE CHECK(sal >1000 AND sal<2000) );

6.练习

商店售货系统表设计案例

现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成：

商品表 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之间

-- 使用约束的课堂练习 -- 1,创建数据库 CREATE DATABASE shop_db; -- 2,商品表 goods CREATE TABLE goods( goods_id INT PRIMARY KEY, -- 商品号 goods_name VARCHAR(64) NOT NULL DEFAULT '', -- 商品名 unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 -- 单价 CHECK(unitprice BETWEEN 1.0 AND 9999.99), category INT NOT NULL DEFAULT 0, -- 商品类别 provider VARCHAR(64) NOT NULL DEFAULT '' -- 供应商 ); -- 3,客户表 customer CREATE TABLE customer( customer_id CHAR(8) PRIMARY KEY, -- 客户号 `name` VARCHAR(64) NOT NULL DEFAULT '', -- 姓名 address VARCHAR(64) NOT NULL DEFAULT '', -- 地址 email VARCHAR(64) UNIQUE NOT NULL, -- 电邮 sex ENUM('男','女') NOT NULL, -- 使用枚举类型，性别 card_Id CHAR(18) -- 身份证 ); -- 4,购买表 purchase CREATE TABLE purchase( order_id INT PRIMARY KEY, -- 订单号 customer_id CHAR(8) NOT NULL DEFAULT '', -- 客户号 goods_id INT NOT NULL DEFAULT 0, -- 商品号 num INT NOT NULL DEFAULT 0, -- 购买数量 FOREIGN KEY(customer_id) REFERENCES customer(customer_id), FOREIGN KEY(goods_id) REFERENCES goods(goods_id) ); DESC goods; DESC customer; DESC purchase;

7.自增长

语法

字段名 整型 primary key auto_increment

例子：在某张表中，存在一个id列（整数）
我们希望在添加记录的时候，该列从1开始自动地增长，应该怎么处理？

-- 自增长演示 -- 创建表 CREATE TABLE t24( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(32) NOT NULL DEFAULT '', `name` VARCHAR(32) NOT NULL DEFAULT '' ); DESC t24; -- 测试自增长的使用 INSERT INTO t24 VALUES(NULL,'jack@qq.com','jack'); INSERT INTO t24 VALUES(NULL,'tom@qq.com','tom'); INSERT INTO t24(email,`name`) VALUES('hsp@qq.com','hsp'); SELECT * FROM t24;

自增长使用细节

一般来说自增长是和primary key配合使用的
自增长也可以单独使用 [但是需要配合一个unique]
自增长修饰的字段为整数型（虽然小数也可以但是很少这样使用）
自增长默认从1开始，也可以通过如下命令来修改
```
alter table 表名 auto_increment = xxx;
```
如果添加数据时候，给自增长字段（列）指定有值，则以指定的值为准。一旦指定了值，下一次自增长就会以指定的值开始增长。因此，如果指定了自增长，一般来说就按自增长的规则来添加数据

-- 修改默认的自增长开始值 CREATE TABLE t25( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(32) NOT NULL DEFAULT '', `name` VARCHAR(32) NOT NULL DEFAULT '' ); ALTER TABLE t25 AUTO_INCREMENT =100; INSERT INTO t25 VALUES(NULL,'jack@qq.com','jack'); -- 指定值 INSERT INTO t25 VALUES(666,'hsp@qq.com','hsp'); -- 一旦指定了值，下一次自增长就会以指定的值开始增长 INSERT INTO t25 VALUES(NULL,'marry@qq.com','marry'); SELECT * FROM t25;