【2】约束

一、Mysql的建表约束

1.主键约束：primary key

目的：唯一确定一条记录。通过给某个字段添加约束，使得该字段不能重复且不能为空。

mysql> create table user(
    -> id int primary key,
    -> name varchar(20)
    -> );

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
| testtype       |
| user           |
+----------------+

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> INSERT INTO user VALUES(1,'Lily');

mysql> INSERT INTO user VALUES(1,'Lily');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

只能有一个。重复则报错。

mysql> INSERT INTO user VALUES(2,'Lily');
Query OK, 1 row affected (0.08 sec)

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | Lily |
|  2 | Lily |
+----+------+

且不能为空。为空则报错。

mysql> INSERT INTO user VALUES(NULL,'Lily');
ERROR 1048 (23000): Column 'id' cannot be null

联合主键：多个字段构成主键。

上个例子中，如果要id和name同为主键怎么整？

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.41 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
| testtype       |
| user           |
| user2          |
+----------------+
4 rows in set (0.01 sec)

只要联合的主键值加起来不重复就可以：

mysql> INSERT INTO user2 VALUES(1,'Zhangsan','1234');
Query OK, 1 row affected (0.14 sec)

mysql> INSERT INTO user2 VALUES(1,'Lisi','4321');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO user2 VALUES(2,'Zhangsan','1234');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO user2 VALUES(1,'Zhangsan','1234');
ERROR 1062 (23000): Duplicate entry '1-Zhangsan' for key 'PRIMARY'

一模一样的就不行，就会报错！当然，联合主键任何一个仍不可以为空！

mysql> INSERT INTO user2 VALUES(NULL,'Zhangsan','1234');
ERROR 1048 (23000): Column 'id' cannot be null

2.自增约束AUTO_INCREMENT

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );

id是主键且为自增约束。

mysql> INSERT INTO user3(name) VALUES('Lily');
Query OK, 1 row affected (0.11 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
|  1 | Lily |
+----+------+
1 row in set (0.02 sec)

mysql> INSERT INTO user3(name) VALUES('Lily');
Query OK, 1 row affected (0.11 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
|  1 | Lily |
|  2 | Lily |
+----+------+
2 rows in set (0.00 sec)

第一次插入数据没有设置id的值，但自动管控id为1。第二次插入数据，自动管控id的值自增为2。

如果在创建表的时候，忘记创建主键约束了怎么办？

创建表之后，主键约束的添加、删除与修改：

mysql> create table user4(
    -> id int,
    -> name varchar(20)
    -> );

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

通过ALTER table 表名 add primary key(字段) 添加主键约束：

mysql> alter table user4 add primary key(id);

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

那又该如何删除那？

ALTER table 表名 drop primary key:

mysql> alter table user4 drop primary key;

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

使用modify修改字段，添加主键约束：

mysql> alter table user4 modify id int primary key;

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

3.唯一约束UNIQUE

约束修饰的字段的值不可以重复：

alter table 表名 add unique(字段)

mysql> create table user5(
    -> id int,
    -> name varchar(20)
    -> );

mysql> alter table user5 add unique(name);

mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

像表中添加数据：

mysql> INSERT INTO user5 VALUES(1,'zhangsan');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO user5 VALUES(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'

mysql> INSERT INTO user5 VALUES(1,'lili');
Query OK, 1 row affected (0.06 sec)

再次添加name=zhangsan不行，因为name的值不可以重复。

除了向上述建表之后通过ALTER添加约束之外，还可以在建表时就添加唯一约束：

mysql> create table user6(
    -> id int,
    -> name varchar(20),
    -> unique(name)
    -> );

或：

mysql> create table user7(
    -> id int,
    -> name varchar(20) unique
    -> );

添加2个唯一约束：

mysql> create table user8(
    -> id int,
    -> name varchar(20),
    -> unique(id,name)
    -> );

mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

此时，id和name值都不能重复？

mysql> INSERT INTO user8 VALUES(1,'zhang');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO user8 VALUES(2,'zhang');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO user8 VALUES(1,'li');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO user8 VALUES(1,'zhang');
ERROR 1062 (23000): Duplicate entry '1-zhang' for key 'id'

mysql> select * from user8;
+------+-------+
| id   | name  |
+------+-------+
|    1 | li    |
|    1 | zhang |
|    2 | zhang |
+------+-------+
3 rows in set (0.00 sec)

由此可见，只要加起来不重复就行。

如何删除唯一约束？

alter table 表名 drop index 字段;

alter table 表名 drop 字段;

没有index，字段name会被删除：

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table user7 drop name;
Query OK, 0 rows affected (0.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user7;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

再添加回去：

mysql> alter table user7 modify name varchar(20) unique;
ERROR 1054 (42S22): Unknown column 'name' in 'user7'

mysql> alter table user7 add name varchar(20) unique;
Query OK, 0 rows affected (0.85 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table user7 modify id int unique;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

总结：

（1）建表的同时添加约束；

（2）建表之后，添加： alter...add...；修改：alter...modify...；删除：alter...drop...

4.非空约束 NOT NULL

修饰的字段不能为空。

mysql> create table user9(
    -> id int,
    -> name varchar(20) NOT NULL
    -> );

mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> INSERT INTO user9 (id) VALUES(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

mysql> INSERT INTO user9 VALUES(1,'Zhang');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO user9 (name) VALUES('lisa');
Query OK, 1 row affected (0.05 sec)

mysql> select * from user9;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Zhang |
| NULL | lisa  |
+------+-------+

5.默认约束default

当我们插入字段值的时候，没有传入值的字段将会使用默认值。

mysql> create table user10(
    -> id int,
    -> name varchar(20),
    -> age int default 22
    -> );

mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | 22      |       |
+-------+-------------+------+-----+---------+-------+

插入数据：

mysql> INSERT INTO user10 VALUES(2,'Leo');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> INSERT INTO user10(id,name) VALUES(2,'Leo');
Query OK, 1 row affected (0.06 sec)

mysql> select * from user10;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    2 | Leo  |   22 |
+------+------+------+

mysql> INSERT INTO user10 VALUES(2,'Leo',18);
Query OK, 1 row affected (0.10 sec)

mysql> select * from user10;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    2 | Leo  |   22 |
|    2 | Leo  |   18 |
+------+------+------+

同样，也可使用add、drop、modify进行添加、删除、修改。

6.外键约束FOREIGN_KEY

此时涉及到两个表：父表与子表。主表与附表。

创建班级表，主键为id：

mysql> create table classes(
    -> id int,
    -> name varchar(20),
    -> primary key(id)
    -> );

创建学生表，主键为id：

mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)
    -> );

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

向班级表中插入数据：

mysql> INSERT INTO classes VALUES(1,'Math');
mysql> INSERT INTO classes VALUES(2,'Bio');
mysql> INSERT INTO classes VALUES(3,'Eng');
mysql> INSERT INTO classes VALUES(4,'Gym');

mysql> select * from classes;
+----+------+
| id | name |
+----+------+
|  1 | Math |
|  2 | Bio  |
|  3 | Eng  |
|  4 | Gym  |
+----+------+

向学生表中插入数据：

mysql> INSERT INTO students VALUES(1001,'Mary',1);
mysql> INSERT INTO students VALUES(1002,'Lily',2);
mysql> INSERT INTO students VALUES(1003,'Simo',3);
mysql> INSERT INTO students VALUES(1004,'Max',4);

mysql> select * from students;
+------+------+----------+
| id   | name | class_id |
+------+------+----------+
| 1001 | Mary |        1 |
| 1002 | Lily |        2 |
| 1003 | Simo |        3 |
| 1004 | Max  |        4 |
+------+------+----------+

注意的是：

1.主表（父表）classes中没有的数据值（例如班级id值没有5），在附表（子表）students中是不可以使用的；

mysql> INSERT INTO students VALUES(1005,'Lose',5);
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 `classes` (`id`))

2.主表中的记录被附表引用，是不可以被删除的；

mysql> delete from classes where id=4;
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 `classes` (`id`))