mysql—约束

本次博客带领大家学习mysql数据库中的约束。

• 基本介绍：约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null、unique、primary key、foreign key和check 五种。

主键约束(primary key)

• primary key(主键)—基本使用

字段名 字段类型 primary key
1

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

1. primary key 不能重复而且不能为 null。

INSERT INTO t17 VALUES(1,'jack','jack@sohu.com');

INSERT INTO t17 VALUES(2,'tom','tom@sohu.com');

INSERT INTO t17 VALUES(1,'ld','ld@sohu.com');

INSERT INTO t17 VALUES(NULL,'ld','ld@sohu.com');
1
2
3
4
5
6
7

2. 一张表最多只能有一个主键，但可以是复合主键。

CREATE TABLE t18(
	id INT ,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY (id,`name`)	
	)
1
2
3
4
5
6

3. 主键的指定方式有两种：

   1. 直接在字段名后指定 ：字段名 primakry key。

   CREATE TABLE t17(
   	id INT PRIMARY KEY,-- 表示id列是主键
   	`name` VARCHAR(32),
   	email VARCHAR(32))
   1
   2
   3
   4

   2. 在表定义最后写primary key(列名)。

   CREATE TABLE t19(
   	id INT ,
   	`name` VARCHAR(32),
   	email VARCHAR(32),
   	PRIMARY KEY(`name`)
   	)
   1
   2
   3
   4
   5
   6

4. 使用desc 表名，可以看到 primakry key 的情况。

DESC t19;
1

非空约束(not null)

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

字段名 字段类型 not null
1

唯一约束(unique)

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

字段名 字段类型 unique
1

1. 如果没有指定 not null，则unique 字段可以有多个null，如果一个列(字段)，是unique not null 使用效果类似 primary key。

INSERT INTO t20 VALUES(NULL,'aaa','aaa@sohu.com');
1

2. 一张表可以有多个unique字段。

CREATE TABLE t21(
	id INT UNIQUE,
	`name` VARCHAR(32) UNIQUE,
	email VARCHAR(32)
	)
1
2
3
4
5

外键约束(foreign key)

• 用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或者是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或者是为null。

基本语法：foreign key (本表的字段名) references 主表名(主键名或unique字段名)
1

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

检查约束(check)

• 用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000_{2000之间，如果不在1000}2000之间就会出错。
• oracle 和 sql server 均支持check，但是mysql 5.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));
	
INSERT INTO t23 VALUES(1,'jack','mid',1);
1
2
3
4
5
6
7

商品销售系统表设计案例

现有一个商店的数据库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)；

建表，在定义中要求声明：

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

CREATE DATABASE shop_db;

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 >=1.0 AND unitprice <=9999.99),
	category INT NOT NULL DEFAULT 0,
	provider VARCHAR(64) NOT NULL DEFAULT '');
	
CREATE TABLE customer(
	customer_id CHAR(8) PRIMARY KEY,
	`name` VARCHAR(64) NOT NULL DEFAULT '',
	address VARCHAR(64) NOT NULL DEFAULT '',
	email VARCHAR(64) NOT NULL UNIQUE,
	sex ENUM('男','女') NOT NULL,
	card_id CHAR(18) UNIQUE);
	
CREATE TABLE purchase(
	order_id INT PRIMARY KEY,
	customer_id CHAR(8) NOT NULL DEFAULT '',
	goods_id INT NOT NULL DEFAULT 0,
	nums 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30