4-2、数据表约束

（1）约束类型

• NOT NULL非空约束
• PRIMARY KEY主键约束
• UNIQUE KEY唯一约束
• DEFAULT默认约束

• FOREIGN KEY外键约束

  （2）非空约束

• null

• not null
• grade不写，则默认可以为null ``` mysql> create table tb2( -> username varchar(20) not null, -> age int unsigned null, -> grade int -> ); Query OK, 0 rows affected (0.01 sec)

mysql> desc tb2; +—————+—————————+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +—————+—————————+———+——-+————-+———-+ | username | varchar(20) | NO | | NULL | | | age | int(10) unsigned | YES | | NULL | | | grade | int(11) | YES | | NULL | | +—————+—————————+———+——-+————-+———-+ 3 rows in set (0.00 sec)

mysql> insert tb2 values(‘tom’,null,null); Query OK, 1 row affected (0.00 sec)

mysql> select * from tb2; +—————+———+———-+ | username | age | grade | +—————+———+———-+ | tom | NULL | NULL | +—————+———+———-+ 1 row in set (0.00 sec)

mysql> insert tb2 values(null,null,null); ERROR 1048 (23000): Column ‘username’ cannot be null mysql> show warnings; +———-+———+—————————————————+ | Level | Code | Message | +———-+———+—————————————————+ | Error | 1048 | Column ‘username’ cannot be null | +———-+———+—————————————————+ 1 row in set (0.00 sec)

<a name="onUBF"></a>
### （3）唯一约束unique
<a name="jI28r"></a>
#### 建表创建unique
- 数据不能重复

mysql> CREATE TABLE tb5( -> id int PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(20) UNIQUE -> ); Query OK, 0 rows affected (0.02 sec)

mysql> INSERT tb5 VALUES(NULL,’tom’); Query OK, 1 row affected (0.00 sec)

mysql> INSERT tb5 VALUES(NULL,’tom’); ERROR 1062 (23000): Duplicate entry ‘tom’ for key ‘name’

mysql> SELECT * from tb5; +——+———+ | id | name | +——+———+ | 1 | tom | +——+———+ 1 row in set (0.00 sec)

- NULL可以重复

mysql> INSERT tb5 VALUES(NULL,NULL); Query OK, 1 row affected (0.01 sec)

mysql> INSERT tb5 VALUES(NULL,NULL); Query OK, 1 row affected (0.00 sec)

mysql> SELECT * from tb5; +——+———+ | id | name | +——+———+ | 3 | NULL | | 4 | NULL | | 1 | tom | +——+———+ 3 rows in set (0.00 sec)

<a name="KxJyW"></a>
#### 删除唯一约束

- MODIFY方式无法删除唯一约束

mysql> ALTER TABLE tb5 MODIFY name VARCHAR(20); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> INSERT tb5 VALUES(NULL,’tom’); ERROR 1062 (23000): Duplicate entry ‘tom’ for key ‘name’

- 删除唯一约束的两种方式
- ALTER TABLE tb5 DROP INDEX name;
- ALTER TABLE tb5 DROP KEY name;

mysql> ALTER TABLE tb5 DROP INDEX name; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM tb5; +———-+——————-+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +———-+——————-+———+——-+————-+————————+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +———-+——————-+———+——-+————-+————————+ 2 rows in set (0.00 sec)

<a name="KyXRk"></a>
#### 建表后添加唯一约束

- 添加唯一约束的两种方式
- ALTER TABLE tb5 MODIFY name VARCHAR(20) UNIQUE;
- ALTER TABLE tb5 ADD UNIQUE(name);

mysql> ALTER TABLE tb5 MODIFY name VARCHAR(20) UNIQUE; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM tb5; +———-+——————-+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +———-+——————-+———+——-+————-+————————+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | UNI | NULL | | +———-+——————-+———+——-+————-+————————+ 2 rows in set (0.00 sec)

<a name="ySi1N"></a>
### （4）默认约束

- default
- 插入记录时，如果没有为字段赋值，则自动赋予默认值

mysql> create table tb6( -> username varchar(20) default ‘tom’, -> age int -> ); Query OK, 0 rows affected (0.02 sec)

mysql> insert tb6 (age) values(20); Query OK, 1 row affected (0.00 sec)

mysql> insert tb6 (username,age) values(‘jack’,20); Query OK, 1 row affected (0.00 sec)

mysql> select * from tb6; +—————+———+ | username | age | +—————+———+ | tom | 20 | | jack | 20 | +—————+———+ 2 rows in set (0.00 sec)

<a name="PI1FG"></a>
### （5）主键

- PRIMARY KEY
- 每张表只能存在一个主键
- 主键自动为NOT NULL
- 主键约束保证记录的唯一性，不能重复

mysql> CREATE TABLE tb4( -> id SMALLINT PRIMARY KEY, -> name VARCHAR(20) -> ); Query OK, 0 rows affected (0.07 sec)

mysql> SHOW COLUMNS FROM tb4; +———-+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +———-+——————-+———+——-+————-+———-+ | id | smallint(6) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +———-+——————-+———+——-+————-+———-+ 2 rows in set (0.01 sec)

mysql> INSERT tb4 VALUES(22,’a’); Query OK, 1 row affected (0.13 sec)

mysql> INSERT tb4 VALUES(21,’a’); Query OK, 1 row affected (0.04 sec)

mysql> INSERT tb4 VALUES(22,’a’); ERROR 1062 (23000): Duplicate entry ‘22’ for key ‘PRIMARY’

mysql> SELECT * FROM tb4; +——+———+ | id | name | +——+———+ | 21 | a | | 22 | a | +——+———+ 2 rows in set (0.00 sec)

<a name="voMDP"></a>
####  AUTO_INCREMENT

- AUTO_INCREMNET自动编号的必须与主键组合使用，但主键可以单独使用
- 添加auto_increment后，主键可以不设置值或者设置为NULL，自动从1开始自增

mysql> create table tb8( -> id int primary key auto_increment, -> name varchar(20) -> ); Query OK, 0 rows affected (0.01 sec)

mysql> insert tb8 values(1,’tom’); Query OK, 1 row affected (0.00 sec)

mysql> insert tb8 values(null,’tom’); Query OK, 1 row affected (0.00 sec)

mysql> select * from tb8; +——+———+ | id | name | +——+———+ | 1 | tom | | 2 | tom | +——+———+ 2 rows in set (0.00 sec)

mysql> insert tb8 values(null,’tom’); Query OK, 1 row affected (0.00 sec)

mysql> select * from tb8; +——+———+ | id | name | +——+———+ | 1 | tom | | 2 | tom | | 3 | tom | +——+———+ 3 rows in set (0.00 sec)

- 删除、添加自增长可以使用modify方式
<a name="Pfp4y"></a>
#### 删除主键

- modify方式无效
- alter table tb8 drop primary key;
<a name="R7hGz"></a>
####  添加主键

- alter table tb8 add primary key(id);
- alter table tb8 modify id int primary key;

<a name="GZXxF"></a>
#### 添加主键
<a name="kcrbr"></a>
### （6）外键约束
<a name="IukRf"></a>
#### 创建外键

- foreign key (pid) references province (id) 
- 外键约束的要求<br />
   - 子表和父表必须使用相同的储存引擎，并且禁止使用临时表<br />
   - 数据表的存储引擎只能为InnoDB<br />
   - 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同<br />
   - 外键列（子表）和参照列（父表）必须创建索引。如果外键列不存在索引的话，MySQl自动创建索引<br />

mysql> create table province( -> id smallint unsigned primary key auto_increment, -> pname varchar(20) not null -> ); Query OK, 0 rows affected (0.02 sec)

//主键会自动创建索引 mysql> show create table province; +—————+————————————————————————————————————————————————————————————————————————————————————————————————————+ | Table | Create Table | +—————+————————————————————————————————————————————————————————————————————————————————————————————————————+ | province | CREATE TABLE province ( id smallint(5) unsigned NOT NULL AUTO_INCREMENT, pname varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +—————+————————————————————————————————————————————————————————————————————————————————————————————————————+ 1 row in set (0.00 sec)

//外键列和参照列必须类型一致 mysql> create table city( -> id smallint unsigned primary key auto_increment, -> cname varchar(10) not null, -> pid bigint, -> foreign key (pid) references province (id) -> ); ERROR 1215 (HY000): Cannot add foreign key constraint mysql> create table city( -> id smallint unsigned primary key auto_increment, -> cname varchar(10) not null, -> pid smallint, -> foreign key (pid) references province (id)
-> ); ERROR 1215 (HY000): Cannot add foreign key constraint

mysql> create table city( -> id smallint unsigned primary key auto_increment, -> cname varchar(10) not null, -> pid smallint unsigned, -> foreign key (pid) references province (id)
-> ); Query OK, 0 rows affected (0.12 sec)

// 如果外键列不存在索引的话，MySQl自动创建索引 mysql> show create table city; +———-+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+ | Table | Create Table | +———-+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+ | city | CREATE TABLE city ( id smallint(5) unsigned NOT NULL AUTO_INCREMENT, cname varchar(10) NOT NULL, pid smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (id), KEY pid (pid), CONSTRAINT city_ibfk_1 FOREIGN KEY (pid) REFERENCES province (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +———-+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+ 1 row in set (0.00 sec)

mysql> show indexes from province; +—————+——————+—————+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +—————+——————+—————+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ | province | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | +—————+——————+—————+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ 1 row in set (0.06 sec)

mysql> show indexes from city; +———-+——————+—————+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +———-+——————+—————+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ | city | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | city | 1 | pid | 1 | pid | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +———-+——————+—————+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ 2 rows in set (0.04 sec)

<a name="Zt7qQ"></a>
#### 删除外键

- alter table user drop foreign key p_id_fk;
<a name="TI1a0"></a>
#### 添加外键

- ALTER TABLE user ADD CONSISTENT p_id_fk foreign key (pid) references person(id);

<br />
<a name="G6yvl"></a>
#### 级联操作

- 外键约束的默认数据联系<br />
   - 父表的参照列有数据后，子表的外键列才可以添加数据<br />
   - 父表和子表有对应数据后，只有子表的数据删除之后才能删除父表的数据

mysql> insert city (cname,pid) values(‘hefei’,1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (t1.city, CONSTRAINT city_ibfk_1 FOREIGN KEY (pid) REFERENCES province (id)) mysql> insert province (pname) values(‘anhui’); Query OK, 1 row affected (0.05 sec)

mysql> select * from province; +——+———-+ | id | pname | +——+———-+ | 1 | anhui | +——+———-+ 1 row in set (0.00 sec)

mysql> insert city (cname,pid) values(‘hefei’,1); Query OK, 1 row affected (0.09 sec)

mysql> select * from city; +——+———-+———+ | id | cname | pid | +——+———-+———+ | 2 | hefei | 1 | +——+———-+———+ 1 row in set (0.00 sec)

mysql> delete from province where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (t1.city, CONSTRAINT city_ibfk_1 FOREIGN KEY (pid) REFERENCES province (id))

mysql> delete from city where id=1; Query OK, 0 rows affected (0.00 sec)

mysql> select * from city; Empty set (0.00 sec)

mysql> delete from province where id=1; Query OK, 1 row affected (0.02 sec)

- 外键约束的参照操作<br />
   - CASCADE：从父表删除/更新后自动更新子表中匹配的行<br />
   - SET NULL：从父表删除/更新后自设置子表中的外键列为NULL（使用该选项的前提是子表列没有指定NOT NULL）<br />
   - RESTRICT：拒绝对父表的删除更新操作<br />
   - NO ACTION：标准SQL关键字，与RESTRICT相同<br />
- on delete cascade

mysql> create table city1( -> id smallint unsigned primary key auto_increment, -> cname varchar(20) not null, -> pid smallint unsigned, -> foreign key (pid) references province (id) on delete cascade
-> ); Query OK, 0 rows affected (0.02 sec)

mysql> select * from province; +——+————-+ | id | pname | +——+————-+ | 2 | beijing | +——+————-+ 1 row in set (0.00 sec)

mysql> select * from city1; +——+———-+———+ | id | cname | pid | +——+———-+———+ | 3 | tom | 2 | +——+———-+———+ 1 row in set (0.00 sec)

mysql> delete from province where id=2; Query OK, 1 row affected (0.02 sec)

mysql> select * from city1; Empty set (0.00 sec)

- 外键约束的on delete set null<br />
   - 从父表删除或者更新行，则设置子表中的外键列为NULL.使用该选项的前提是子表列没有指定NOT NULL

mysql> create table city2(
-> id smallint unsigned primary key auto_increment, -> cname varchar(20) not null, -> pid smallint unsigned, -> foreign key (pid) references province (id) on delete set null
-> ); Query OK, 0 rows affected (0.09 sec)

mysql> insert province (pname) values(‘shanghai’); Query OK, 1 row affected (0.02 sec)

mysql> select * from province; +——+—————+ | id | pname | +——+—————+ | 3 | shanghai | +——+—————+ 1 row in set (0.00 sec)

mysql> insert city2 (cname,pid) values(‘baoshan’,3); Query OK, 1 row affected (0.02 sec)

mysql> select * from city2; +——+————-+———+ | id | cname | pid | +——+————-+———+ | 1 | baoshan | 3 | +——+————-+———+ 1 row in set (0.00 sec)

mysql> delete from province where id = 3; Query OK, 1 row affected (0.10 sec)

mysql> select * from city2; +——+————-+———+ | id | cname | pid | +——+————-+———+ | 1 | baoshan | NULL | +——+————-+———+ 1 row in set (0.00 sec) ```

• ON UPDATE CASCADE：级联更新