【无标题】

高级约束

设置在表头上，作用，控制表头赋值的

一、主键

primary?key

表头不能赋null值且值唯一

1、主键的创建

mysql> create table db3.t7( 姓名 char(3),age int default 19,身份证 char(18),primary key(身份证) );

mysql> create table db3.t8( 姓名 char(3),age int default 19,身份证 char(18) primary key );

2、主键的查看

mysql> desc db3.t7;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| 姓名      | char(3)  | YES  |     | NULL    |       |
| age       | int      | YES  |     | 19      |       |
| 身份证    | char(18) | NO   | PRI | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc db3.t8;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| 姓名      | char(3)  | YES  |     | NULL    |       |
| age       | int      | YES  |     | 19      |       |
| 身份证    | char(18) | NO   | PRI | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3、主键的删除

mysql> alter table db3.t8 drop primary key;
Query OK, 0 rows affected (1.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db3.t8;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| 姓名      | char(3)  | YES  |     | NULL    |       |
| age       | int      | YES  |     | 19      |       |
| 身份证    | char(18) | NO   |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table db3.t8 modify 身份证 char(18);
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db3.t8;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| 姓名      | char(3)  | YES  |     | NULL    |       |
| age       | int      | YES  |     | 19      |       |
| 身份证    | char(18) | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4、主键的添加

mysql> alter table db3.t8 add primary key(身份证);
Query OK, 0 rows affected (0.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db3.t8;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| 姓名      | char(3)  | YES  |     | NULL    |       |
| age       | int      | YES  |     | 19      |       |
| 身份证    | char(18) | NO   | PRI | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5、验证功能

mysql> insert into db3.t8(姓名,身份证) values("张三","1111");    # 正常
mysql> insert into db3.t8(姓名,身份证) values("张五","2222");    # 正常
mysql> insert into db3.t8(姓名,身份证) values("张五",null);    # 空 报错
mysql> insert into db3.t8(姓名,身份证) values("张六","2222");    # 重复 报错

6、复合主键

多个表头一起做主键 必须一起创建或删除

表头的值不同时重复即可

1）复合主键的查看

mysql> desc db3.t9;

2）创建

mysql> create table db3.t9(
    -> cip varchar(15),
    -> port int,
    -> status enum("allow","deny"),
    -> primary key(cip,port)
    -> );
Query OK, 0 rows affected (0.39 sec)

3）删除

mysql> alter table db3.t9 drop primary key;

4）添加

mysql> alter table db3.t9 add primary key(cip,port);

5）验证?

mysql> insert into db3.t9 values("1.1.1.1",22,"allow");
Query OK, 1 row affected (0.04 sec)

mysql> insert into db3.t9 values("1.1.1.1",22,"deny");
ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 't9.PRIMARY'

mysql> insert into db3.t9 values("1.1.1.1",80,"allow");
Query OK, 1 row affected (0.08 sec)

7、主键与auto_increment

自增长

给表头加了自增长的设置后 不给表头赋值时 通过自加1的计算结果赋值

自增长必须和主键

mysql> create table db3.t10(
    -> mun int primary key auto_increment,
    -> name char(3),
    -> age int
    -> );
Query OK, 0 rows affected (1.00 sec)

mysql> desc db3.t10;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| mun   | int     | NO   | PRI | NULL    | auto_increment |
| name  | char(3) | YES  |     | NULL    |                |
| age   | int     | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into db3.t10(name,age) values("b",21);
Query OK, 1 row affected (0.04 sec)

mysql> insert into db3.t10(name,age) values("a",21);
Query OK, 1 row affected (0.09 sec)

mysql> select * from db3.t10;
+-----+------+------+
| mun | name | age  |
+-----+------+------+
|   1 | b    |   21 |
|   2 | a    |   21 |
+-----+------+------+
2 rows in set (0.00 sec)

删除

mysql> desc db3.t10;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| mun   | int     | NO   | PRI | NULL    | auto_increment |
| name  | char(3) | YES  |     | NULL    |                |
| age   | int     | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table db3.t10 modify mun int;

mysql> desc db3.t10;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| mun   | int     | NO   | PRI | NULL    |       |
| name  | char(3) | YES  |     | NULL    |       |
| age   | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table db3.t10 drop primary key;

mysql> desc db3.t10;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| mun   | int     | NO   |     | NULL    |       |
| name  | char(3) | YES  |     | NULL    |       |
| age   | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

?二、外键

给外键表头赋值时，值在参考的表的主键表头值里选择

salary.employee_id 外键 ----参考-----> employees.employee_id

1、创建外键

# 表头类型 存储引擎
create table 库.表(
表头名列表,
foreign key(表a的表头名) references 库.表b(表b的表头名)
)engine=innodb;

create table 库.表(
表头名列表,
foreign key(表a的表头名) references 库.表b(表b的表头名)
on update cascade on delete cascade
);

2、创建员工表

mysql> create table db3.yg_tab( yg_id int primary key auto_increment, name char(4) );

3、存储员工信息

mysql> insert into db3.yg_tab(name) values("tom"),("lucy"),("lili");
mysql> select * from db3.yg_tab;

4、创建外键表 工资表

mysql> create table db3.gz_tab( gz_id int , 工资 float, foreign key(gz_id) references db3.yg_tab(yg_id) on update cascade on delete cascade )engine=innodb;

5、删除外键

mysql> show create table db3.gz_tab\G
*************************** 1. row ***************************
       Table: gz_tab
Create Table: CREATE TABLE `gz_tab` (
  `gz_id` int DEFAULT NULL,
  `工资` float DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> alter table db3.gz_tab drop foreign key gz_tab_ibfk_1;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table db3.gz_tab\G
*************************** 1. row ***************************
       Table: gz_tab
Create Table: CREATE TABLE `gz_tab` (
  `gz_id` int DEFAULT NULL,
  `工资` float DEFAULT NULL,
  KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

6、添加外键

mysql> alter table db3.gz_tab add foreign key(gz_id) references db3.yg_tab(yg_id) on update cascade on delete cascade;
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table db3.gz_tab\G                                                                     *************************** 1. row ***************************
       Table: gz_tab
Create Table: CREATE TABLE `gz_tab` (
  `gz_id` int DEFAULT NULL,
  `工资` float DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

7、验证外键功能

1）外键字段的值必须在参考表字段值范围内

mysql> select * from db3.yg_tab;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | tom  |
|     2 | lucy |
|     3 | lili |
+-------+------+
3 rows in set (0.00 sec)

mysql> select * from db3.gz_tab;
Empty set (0.00 sec)

mysql> desc db3.gz_tab;
+--------+-------+------+-----+---------+-------+
| Field  | Type  | Null | Key | Default | Extra |
+--------+-------+------+-----+---------+-------+
| gz_id  | int   | YES  | MUL | NULL    |       |
| 工资   | float | YES  |     | NULL    |       |
+--------+-------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into db3.gz_tab values(1,10000);
Query OK, 1 row affected (0.07 sec)

mysql> insert into db3.gz_tab values(2,20000);
Query OK, 1 row affected (0.06 sec)

mysql> insert into db3.gz_tab values(3,30000);
Query OK, 1 row affected (0.10 sec)

# 没有的4号员工 工资表插入记录报错
mysql> insert into db3.gz_tab values(4,40000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db3`.`gz_tab`, CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)

# 员工表 插入编号4的员工
mysql> insert into db3.yg_tab(name) values("bob");
Query OK, 1 row affected (0.04 sec)

# 可以给4号员工 发工资了 
mysql> insert into db3.gz_tab values(4,40000);
Query OK, 1 row affected (0.04 sec)

2）验证同步更新（on update cascade）

mysql> select * from db3.yg_tab;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | tom  |
|     2 | lucy |
|     3 | lili |
|     4 | bob  |
+-------+------+
4 rows in set (0.00 sec)

mysql> select * from db3.gz_tab;
+-------+--------+
| gz_id | 工资   |
+-------+--------+
|     1 |  10000 |
|     2 |  20000 |
|     3 |  30000 |
|     4 |  40000 |
+-------+--------+
4 rows in set (0.00 sec)

mysql> update db3.yg_tab set yg_id=8 where yg_id=4;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from db3.yg_tab;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | tom  |
|     2 | lucy |
|     3 | lili |
|     8 | bob  |
+-------+------+
4 rows in set (0.00 sec)

mysql> select * from db3.gz_tab;
+-------+--------+
| gz_id | 工资   |
+-------+--------+
|     1 |  10000 |
|     2 |  20000 |
|     3 |  30000 |
|     8 |  40000 |
+-------+--------+
4 rows in set (0.01 sec)

3）验证同步删除（on delete cascade）

mysql> delete from db3.yg_tab where yg_id=2;
Query OK, 1 row affected (0.07 sec)

mysql> select * from db3.yg_tab;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | tom  |
|     3 | lili |
|     8 | bob  |
+-------+------+
3 rows in set (0.00 sec)

mysql> select * from db3.gz_tab;
+-------+--------+
| gz_id | 工资   |
+-------+--------+
|     1 |  10000 |
|     3 |  30000 |
|     8 |  40000 |
+-------+--------+
3 rows in set (0.00 sec)

8、外键使用事项

# 被参考的表不能删除
mysql> drop table db3.yg_tab;
ERROR 3730 (HY000): Cannot drop table 'yg_tab' referenced by a foreign key constraint 'gz_tab_ibfk_1' on table 'gz_tab'.
mysql> 

给 db3.gz_tab 表的 gz_id 表头 加主键标签
保证每个员工只能发1遍工资  且有员工编号的员工才能发工资

# 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键 
mysql> insert into db3.gz_tab values(1,70000);
mysql> insert into db3.gz_tab values(null,80000);
mysql> select * from db3.gz_tab;
+-------+--------+
| gz_id | 工资   |
+-------+--------+
|     1 |  10000 |
|     3 |  30000 |
|     8 |  40000 |
|     1 |  70000 |
|  NULL |  80000 |
+-------+--------+
5 rows in set (0.00 sec)
mysql> delete from db3.gz_tab;
mysql> alter table db3.gz_tab add primary key(gz_id);

保证每个员工只能发1遍工资  且有员工编号的员工才能发工资
mysql> desc db3.gz_tab;
+--------+-------+------+-----+---------+-------+
| Field  | Type  | Null | Key | Default | Extra |
+--------+-------+------+-----+---------+-------+
| gz_id  | int   | NO   | PRI | NULL    |       |
| 工资   | float | YES  |     | NULL    |       |
+--------+-------+------+-----+---------+-------+
2 rows in set (0.00 sec)