系统环境

Ubuntu14.04 64位版本

约束

作用

给表添加约束值,从而约束用户操作数据库的行为

常见约束
  1. 默认值约束 (default)
  2. 非空约束 (not null)
  3. 唯一值约束 (unique)
  4. 主键约束 (primary key)
  5. 自增长约束 (auto_increment)
  6. 外键约束 (foreign key)

准备

启动 MySQL 服务
# 启动服务
pinsily@zhu ~$ sudo service mysql start

# 以 root 用户登录,并输入密码
pinsily@zhu ~$ mysql -u root -p
Enter password:
说明

下面的实例以数据库 pinsily_user 中的表 student 进行示范,且每次示范完都把 student 表删掉之后重新创建

  • 连接数据库
mysql> USE pinsily_user;
  • 删除表
mysql> DROP TABLE student;

默认值约束 (default)

当没有插入值的时候默认值生效

# 创建 student 表
mysql> CREATE TABLE student(id int,name varchar(20) default 'pinsily');

# 插入值,不插入姓名
mysql> INSERT INTO student(id) VALUES(1);

# 查看表内容,没插入的姓名为默认值
mysql> SELECT * FROM student;
+------+---------+
| id   | name    |
+------+---------+
|    1 | pinsily |
+------+---------+
1 row in set (0.00 sec)

撤销 DEFAULT 约束

ALTER TABLE student
ALTER name DROP DEFAULT

非空约束(not null)

当某个字段必须要值(不能不插入数值,也不能插入null),就给这个字段添加一个非空约束

# 创建表,name 字段非空
mysql> CREATE TABLE student(id int,name varchar(25) not null);
Query OK, 0 rows affected (0.20 sec)

# 没插入值会报错
mysql> INSERT INTO student(id) VALUES(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

# 插入 null 值也会报错
mysql> INSERT INTO student(id,name) VALUES(1,null);
ERROR 1048 (23000): Column 'name' cannot be null

唯一值约束(unique)

如 id 的值不能出现重复值。这时就要给 id 添加一个唯一约束

# 创建表,id 为唯一值
mysql> CREATE TABLE student(id int unique,name varchar(20));
Query OK, 0 rows affected (0.16 sec)

# 插入值
mysql> INSERT INTO student(id,name) VALUES(1,'pinsily');
Query OK, 1 row affected (0.04 sec)

# 若插入相同的 id 会报错
mysql> INSERT INTO student(id,name) VALUES(1,'peng');
ERROR 1062 (23000): Duplicate entry '1' for key 1

主键约束 (primary key)

通常情况下,我们会给每张表都会设置一个主键字段,用来标记记录的唯一性,一般每张表都会设置 id 字段成为主键
简单来讲,主键拥有 唯一性和非空性

# 新建表,将 id 字段设置为主键
mysql> CREATE TABLE student(id int primary key,name varchar(25));
Query OK, 0 rows affected (0.17 sec)

# 插入新值
mysql> INSERT INTO student(id,name) VALUES(1,'peng');
Query OK, 1 row affected (0.07 sec)

# 若插入相同 id 会报错,唯一性
mysql> INSERT INTO student(id,name) VALUES(1,'pinsily');
ERROR 1062 (23000): Duplicate entry '1' for key 1

# 若插入空值会报错,非空性
mysql> INSERT INTO student(name) VALUES('pinsily');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

自增长约束 (auto_increment)

一个表中的id值通常都是一次+1的,这样的需求其实不需要我们每次手动去插入数值,数据库会自动帮我们插入数据,需要配合主键使用

# 新建表,id 自动增加
mysql> CREATE TABLE student(id int primary key auto_increment,name varchar(25));
Query OK, 0 rows affected (0.18 sec)

# 插入
mysql> INSERT INTO student(name) VALUES('pinsily');
Query OK, 1 row affected (0.07 sec)

# 插入
mysql> INSERT INTO student(name) VALUES('peng');
Query OK, 1 row affected (0.10 sec)

# 插入
mysql> INSERT INTO student(name) VALUES('liang');
Query OK, 1 row affected (0.07 sec)

# 查看表,可以看到 id 自动增加了
mysql> SELECT * FROM student;
+----+---------+
| id | name    |
+----+---------+
|  1 | pinsily |
|  2 | peng    |
|  3 | liang   |
+----+---------+
3 rows in set (0.00 sec)


外键约束 (foreign key)

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY

FOREIGN KEY 约束用于预防破坏表之间连接的动作

FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一

假设 student 表如下:

s_id

name

c_id

1

A

computer science 1

2

B

computer science 1

3

C

computer science 1

4

D

computer science 2

5

E

computer science 2

6

F

computer science 2

但是觉得 class 重复太多,浪费了,这时候可以使用分成两个表了
建立 class 表

c_id

class

1

computer science 1

2

computer science 2

建立 student 表

s_id

name

c_id

1

A

1

2

B

1

3

C

1

4

D

2

5

E

2

6

F

2

这时 student 表中的外键 c_id 关联了 class 表中的主键 c_id

# 创建 class 表,即外键指向的表
mysql> CREATE TABLE class(c_id int primary key,class varchar(55));
Query OK, 0 rows affected (0.16 sec)

# 插入数据
mysql> INSERT INTO class(c_id,class) VALUES(1,'computer science 1');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO class(c_id,class) VALUES(2,'computer science 2');
Query OK, 1 row affected (0.07 sec)

mysql> select * from class;
+------+--------------------+
| c_id | class              |
+------+--------------------+
|    1 | computer science 1 |
|    2 | computer science 2 |
+------+--------------------+
2 rows in set (0.00 sec)
# 创建 student 表,外键 c_id 指向 class 中的主键 c_id 
mysql> CREATE TABLE student(
    -> s_id int primary key auto_increment,
    -> name varchar(20),
    -> c_id int,
    -> FOREIGN KEY (c_id) REFERENCES class(c_id));
Query OK, 0 rows affected (0.14 sec)

# 插入数据并查询
mysql> INSERT INTO student(name,c_id) VALUES('A',1) ;
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO student(name,c_id) VALUES('B',1) ;
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO student(name,c_id) VALUES('C',1) ;
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO student(name,c_id) VALUES('D',2) ;
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO student(name,c_id) VALUES('E',2) ;
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO student(name,c_id) VALUES('F',2) ;
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM student;
+------+------+------+
| s_id | name | c_id |
+------+------+------+
|    1 | A    |    1 |
|    2 | B    |    1 |
|    3 | C    |    1 |
|    4 | D    |    2 |
|    5 | E    |    2 |
|    6 | F    |    2 |
+------+------+------+
6 rows in set (0.00 sec)

# 若插入外键表中不存在的数据,会报错
mysql> INSERT INTO student(name,c_id) VALUES('F',3) ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pinsily_user/student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`c_id`))