MySQL数据库约束详解

基本介绍

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

在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;