8.1 约束简介

  1. 含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性
  4. 分类：
  5.     not null：非空，用于保证该字段的值不能为空。比如姓名、学号
  6.     default：默认，用于保证该字段有默认值
  7.   primary key：主键，用于保证该字段的值具有唯一性，并且非空
  8.   unique：唯一，用于保证该字段的值具有唯一性，可以为空
  9.   foreign key：外键，限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
  12. 添加约束的时机：
  13.     1.创建表时
  14.   2.修改表时
  17. 约束的分类：
  18.         列级约束：六大约束都支持，但外键约束没有效果
  19.       表级约束：除了非空、默认、其他都支持
  22. create table 表名(
  23.             字段名  字段类型  列级约束,
  24.           字段名     字段约束,
  25.           表级约束
  27. )

8.2 案例

列级约束

  3. mysql> create table major(id int primary key,majorname varchar(20));
  4. Query OK, 0 rows affected (0.00 sec)
  7. mysql> create table stuinfo(
  8.   id int primary key, 
  9.   stuname varchar(20) not null,
  10.   gender char(1) check (gender ='男'or gender='女'),
  11.   seat int unique,
  12.   age int default 18,
  13.   majorId int  references major(id));
  14. Query OK, 0 rows affected (0.00 sec)
  17. mysql> desc stuinfo;
  18. +---------+-------------+------+-----+---------+-------+
  19. | Field   | Type        | Null | Key | Default | Extra |
  20. +---------+-------------+------+-----+---------+-------+
  21. | id      | int(11)     | NO   | PRI | NULL    |       |
  22. | stuname | varchar(20) | NO   |     | NULL    |       |
  23. | gender  | char(1)     | YES  |     | NULL    |       |
  24. | seat    | int(11)     | YES  | UNI | NULL    |       |
  25. | age     | int(11)     | YES  |     | 18      |       |
  26. | majorId | int(11)     | YES  |     | NULL    |       |
  27. +---------+-------------+------+-----+---------+-------+
  28. 6 rows in set (0.06 sec)
  32. mysql> mysql> show index from stuinfo;
  33. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+-----+
  34. | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Nut |
  35. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+-----+
  36. | stuinfo |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |     |
  37. | stuinfo |          0 | seat     |            1 | seat        | A         |           0 |     NULL | NULL   | YE  |
  38. +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+-----+
  39. 2 rows in set (0.00 sec)

表级约束

  1. mysql> create table stuinfo(
  2.   id int, 
  3.   stuname varchar(20),
  4.   gender char(1),
  5.   seat int,
  6.   age int,
  7.   majorId int, 
  8.   primary key(id), 
  9.   unique(seat)
  10. );

主键和唯一的大比对
唯一性 是否允许为空 一个表中 是否允许组合
主键 √ × 至多有1个 可以，不推荐
唯一 √ √ 可以有多个 可以，不推荐

修改表时添加约束

  1. mysql> create table stuinfo(id int,stuname varchar(20),seat int,age int,majorid int);
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> desc stuinfo;
  5. +---------+-------------+------+-----+---------+-------+
  6. | Field   | Type        | Null | Key | Default | Extra |
  7. +---------+-------------+------+-----+---------+-------+
  8. | id      | int(11)     | YES  |     | NULL    |       |
  9. | stuname | varchar(20) | YES  |     | NULL    |       |
  10. | seat    | int(11)     | YES  |     | NULL    |       |
  11. | age     | int(11)     | YES  |     | NULL    |       |
  12. | majorid | int(11)     | YES  |     | NULL    |       |
  13. +---------+-------------+------+-----+---------+-------+
  14. 5 rows in set (0.00 sec)
  18. mysql> alter table stuinfo modify column stuname varchar(20) not null;
  19. Query OK, 0 rows affected (0.00 sec)
  20. Records: 0  Duplicates: 0  Warnings: 0
  22. mysql> desc stuinfo;
  23. +---------+-------------+------+-----+---------+-------+
  24. | Field   | Type        | Null | Key | Default | Extra |
  25. +---------+-------------+------+-----+---------+-------+
  26. | id      | int(11)     | YES  |     | NULL    |       |
  27. | stuname | varchar(20) | NO   |     | NULL    |       |
  28. | seat    | int(11)     | YES  |     | NULL    |       |
  29. | age     | int(11)     | YES  |     | NULL    |       |
  30. | majorid | int(11)     | YES  |     | NULL    |       |
  31. +---------+-------------+------+-----+---------+-------+
  32. 5 rows in set (0.00 sec)
  36. mysql> alter table stuinfo modify column age int default 18;
  37. Query OK, 0 rows affected (0.00 sec)
  38. Records: 0  Duplicates: 0  Warnings: 0
  40. mysql> desc stuinfo;
  41. +---------+-------------+------+-----+---------+-------+
  42. | Field   | Type        | Null | Key | Default | Extra |
  43. +---------+-------------+------+-----+---------+-------+
  44. | id      | int(11)     | YES  |     | NULL    |       |
  45. | stuname | varchar(20) | NO   |     | NULL    |       |
  46. | seat    | int(11)     | YES  |     | NULL    |       |
  47. | age     | int(11)     | YES  |     | 18      |       |
  48. | majorid | int(11)     | YES  |     | NULL    |       |
  49. +---------+-------------+------+-----+---------+-------+
  50. 5 rows in set (0.00 sec)
  52. mysql> 
  55. mysql> alter table stuinfo modify column id int primary key;
  56. Query OK, 0 rows affected (0.00 sec)
  57. Records: 0  Duplicates: 0  Warnings: 0
  59. mysql> desc stuinfo;
  60. +---------+-------------+------+-----+---------+-------+
  61. | Field   | Type        | Null | Key | Default | Extra |
  62. +---------+-------------+------+-----+---------+-------+
  63. | id      | int(11)     | NO   | PRI | NULL    |       |
  64. | stuname | varchar(20) | NO   |     | NULL    |       |
  65. | seat    | int(11)     | YES  |     | NULL    |       |
  66. | age     | int(11)     | YES  |     | 18      |       |
  67. | majorid | int(11)     | YES  |     | NULL    |       |
  68. +---------+-------------+------+-----+---------+-------+
  69. 5 rows in set (0.00 sec)

8.3 标识列

标识列又称为自增长列，含义：可以不用手动的插入值，系统提供默认的序列值