MySQL数据库设计⽅法之字段属性
• 表字段在设计时还需考虑字段属性
• NOT NULL代表表中此列的数据必须存在，默认是NULL容许为空
• 主键(primary key)代表此表的所有数据都可以被主键⾥的字段区分，创建完主键则默认会在对应字段上创建唯⼀性索引，且每个主键字段都需要是NOT NULL，⼀个表上只允许
有⼀个主键
• 外键(foreign key)可以将两个表的数据建⽴映射关系，并定义不同的外键约束条件以保证数据的⼀致性，通常为⼀个⽗表⼀个⼦表，⼦表中的数据映射到⽗表对应的列
mysql> use course;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_course |
+------------------+
| course |
| dept |
| myset |
| score |
| score_avg |
| shirts |
| students |
| teacher |
| teacher_history |
| temp |
| temp2 |
| temp3 |
| temp5 |
| test |
+------------------+
14 rows in set (0.00 sec)
mysql> select * from teacher;
+-----+-----------+---------+
| id | name | dept_id |
+-----+-----------+---------+
| 1 | zhangsan | 2 |
| 2 | zhangsan3 | 3 |
| 3 | zhangsan3 | 3 |
| 4 | zhangsan3 | 3 |
| 5 | zhangsan3 | 3 |
| 6 | zhangsan3 | 3 |
| 7 | zhangsan3 | 3 |
| 8 | zhangsan3 | 3 |
| 10 | a | 1 |
| 100 | a | 1 |
| 101 | a | 1 |
+-----+-----------+---------+
11 rows in set (0.00 sec)
mysql> show create table teacher;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Table | Create Table +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| teacher | CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`dept_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `for_teacher_1` (`dept_id`),
CONSTRAINT `for_teacher_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)
mysql> select * from dept;
+----+------------------+
| id | dept_name |
+----+------------------+
| 1 | Education |
| 2 | Computer Science |
| 3 | Mathematics |
+----+------------------+
3 rows in set (0.00 sec)
mysql> insert into teacher values(10000,'a',4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`course`.`teacher`, CONSTRAINT `for_teacher_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
mysql> delete from dept where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`course`.`teacher`, CONSTRAINT `for_teacher_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
mysql> update dept set id=4where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`course`.`teacher`, CONSTRAINT `for_teacher_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
mysql> alter table teacher drop foreign key for_teacher_1;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table teacher add constraint for_teacher_1 FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) on update cascade on delete cascade;
Query OK, 11 rows affected (0.16 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> update dept set id=4where id=3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+----+------------------+
| id | dept_name |
+----+------------------+
| 1 | Education |
| 2 | Computer Science |
| 4 | Mathematics |
+----+------------------+
3 rows in set (0.01 sec)
mysql> select * from teacher;
+-----+-----------+---------+
| id | name | dept_id |
+-----+-----------+---------+
| 1 | zhangsan | 2 |
| 2 | zhangsan3 | 4 |
| 3 | zhangsan3 | 4 |
| 4 | zhangsan3 | 4 |
| 5 | zhangsan3 | 4 |
| 6 | zhangsan3 | 4 |
| 7 | zhangsan3 | 4 |
mysql> delete from dept where id=4
-> ;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`course`.`course`, CONSTRAINT `for_course_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`))
mysql> show create table course;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Table | Create Table +--------+--------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| course | CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`course_name` varchar(64) DEFAULT NULL,
`teacher_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `for_course_1` (`teacher_id`),
CONSTRAINT `for_course_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)
mysql> alter table course drop foreign key for_course_1;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from dept where id=4
-> ;
Query OK, 1 row affected (0.08 sec)
mysql> select * from dept;
+----+------------------+
| id | dept_name |
+----+------------------+
| 1 | Education |
| 2 | Computer Science |
+----+------------------+
2 rows in set (0.00 sec)
mysql> select * from teacher;
+-----+----------+---------+
| id | name | dept_id |
+-----+----------+---------+
| 1 | zhangsan | 2 |
| 10 | a | 1 |
| 100 | a | 1 |
| 101 | a | 1 |
+-----+----------+---------+
4 rows in set (0.00 sec)
mysql> alter table teacher drop foreign key for_teacher_1;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table teacher add constraint for_teacher_1 FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) on update cascade on delete cascade;
Query OK, 4 rows affected (0.15 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table teacher add constraint for_teacher_1 FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) on update set null on delete set null;
ERROR 1830 (HY000): Column 'dept_id' cannot be NOT NULL: needed in a foreign key constraint 'f
or_teacher_1' SET NULL
mysql> show create table teacher;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Table | Create Table +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| teacher | CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`dept_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `for_teacher_1` (`dept_id`),
CONSTRAINT `for_teacher_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)
mysql> alter table teacher modify dept_id int null;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table teacher;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Table | Create Table +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| teacher | CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
数据库属性的概念KEY `for_teacher_1` (`dept_id`),
CONSTRAINT `for_teacher_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)
mysql> alter table teacher add constraint for_teacher_1 FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) on update set null on delete set null;
ERROR 1826 (HY000): Duplicate foreign key constraint name 'for_teacher_1'
mysql> alter table teacher drop foreign key for_teacher_1;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table teacher add constraint for_teacher_1 FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) on update set null on delete set null;
Query OK, 4 rows affected (0.20 sec)
Records: 4 Duplicates: 0 Warnings: 0
• Index_name代表外键ID，默认情况下MySQL会在⼦表上创建⼀个外键索引
• MySQL推荐在⽗表和⼦表的相关字段上都创建索引，以避免全表扫描
• 当创建好外键后，任何对⼦表的插⼊和修改操作如果对应的值没有在⽗表中有对应，都会被MySQL拒绝
• 当在⽗表上update和delete操作时，对⼦表中对应数据的操作依赖设置
• Cascade代表⼦表中的数据也⾃动update和delete
• Set null代表⼦表中的数据⾃动修改成null
• Restrict(默认)代表如果⼦表中有对应的数据，则拒绝⽗表上的update和delete操作
• No action在MySQL中的含义和restrict⼀样
• Set default代表将⼦表中的数据⾃动修改成default值
mysql> select * from teacher;
+-----+----------+---------+
| id | name | dept_id |
+-----+----------+---------+
mysql> select * from dept;
+----+------------------+
| id | dept_name |
+----+------------------+
| 1 | Education |
| 2 | Computer Science |
+----+------------------+
2 rows in set (0.01 sec)
mysql> update dept set id=3where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`course`.`students`, CONSTRAINT `for_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)) mysql>
mysql>
mysql> truncate table students;
Query OK, 0 rows affected (0.17 sec)
mysql> update dept set id=3where id=1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+----+------------------+
| id | dept_name |
+----+------------------+
| 2 | Computer Science |
| 3 | Education |
+----+------------------+
2 rows in set (0.01 sec)
mysql> select * from teacher;
+-----+----------+---------+
| id | name | dept_id |
+-----+----------+---------+
| 1 | zhangsan | 2 |
| 10 | a | NULL |
| 100 | a | NULL |
| 101 | a | NULL |
+-----+----------+---------+
4 rows in set (0.00 sec)
mysql> delete from dept where id=2;
Query OK, 1 row affected (0.06 sec)
mysql> select * from dept;
+----+-----------+
| id | dept_name |
+----+-----------+
| 3 | Education |
+----+-----------+
1 row in set (0.00 sec)
mysql> select * from teacher;
+-----+----------+---------+
| id | name | dept_id |
+-----+----------+---------+
| 1 | zhangsan | NULL |
| 10 | a | NULL |
| 100 | a | NULL |
| 101 | a | NULL |
+-----+----------+---------+
4 rows in set (0.00 sec)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论