【MySQL】-- 數據庫約束

文章目錄

1. 什么是數據庫約束
2. 約束類型
3. NOT NULL 非空約束
4. DEFALUT 默認值約束
5. UNIQUE 唯一約束
6. PRIMARY KEY 主鍵約束
- 6.1 自增主鍵
- 6.1 一個自增主鍵包含多個列
7. FOREIGN KEY 外鍵約束
8. CHECK 約束

1. 什么是數據庫約束

數據庫約束是指對數據庫表中的數據所施加的規則或條件，用于確保數據的準確性和可靠性。這些約束可以是基于數據類型、值范圍、唯一性、非空等規則，以確保數據的正確性和相容性。

數據庫約束時關系型數據庫的一個重要功能，主要的作用是保證數據的有效性，也可以理解為數據的正確性（數據本身是否正確，關聯關系是否正確）。

人工檢查數據的完整性工作量非常大，在數據庫中定義一些約束，那么數據在寫入數據庫的時候，就會幫我們做一些校驗。約束一般是在指定的列上創建的。

2. 約束類型

類型	說明
NOTNULL?空約束	指定非空約束的列不能存儲NULL值
DEFALUT默認約束	當沒有給列賦值時使用的默認值
UNIQUE唯一約束	指定唯一約束的列每行數據必須有唯一的值
PRIMARYKEY主鍵約束	NOTNULL和UNIQUE的結合，可以指定一個列或多個列，有助于防止數據重復和提高數據的查詢性能
FOREIGNKEY外鍵約束	外鍵約束是一種關系約束，用于定義兩個表之間的關聯關系，可以確保數據的完整性和一致性
CHECK約束	用于限制列或數據在數據庫表中的值，確保數據的準確性和可靠性

前四個比較常用。

3. NOT NULL 非空約束

定義表時某列不允許為null時，可以為列添加非空約束。

比如創建一個學生表，學生名為null時，這條記錄是不完整的

-- 創建表
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table student(-> id bigint,-> name varchar(20)-> );
Query OK, 0 rows affected (0.03 sec)
-- 插入數據
mysql> insert into student values (1, null);
Query OK, 1 row affected (0.01 sec)
//查詢
mysql> select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.03 sec)

姓名為null，沒有意義。

此時需要約束學生名的列不能為null

-- 創建表
mysql> drop table if exists student;
Query OK, 0 rows affected (0.05 sec)
-- 為name列添加非空約束
mysql> create table student(-> id bigint,-> name varchar(20) not null-> );
Query OK, 0 rows affected (0.05 sec)
-- 插入name為null的數據，會報錯
mysql> insert into student (id, name) values (1, null);
ERROR 1048 (23000): Column 'name' cannot be null
-- 插入非空數據，可以正常插入
mysql> insert into student (id, name) values (1, '張三');
Query OK, 1 row affected (0.03 sec)
-- 查詢數據
mysql> select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | 張三 |
+------+------+
1 row in set (0.00 sec)

查看表結構

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

可以看到student表中的Null列對應的name那一行顯示為NO，就表示name不能為空。

4. DEFALUT 默認值約束

DEFALUT約束用于向列中插入默認值，如果沒有為列設置值，那么會將默認值設置到該列

創建學生表，新增年齡列，并為其設置默認值18

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)-- 創建表
mysql> create table student(-> id bigint primary key auto_increment,-> name varchar(20) not null,-> age int default 18-> );
Query OK, 0 rows affected (0.02 sec)-- 查看表結構
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| age   | int         | YES  |     | 18      |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

沒有指定值的就用默認值填充

-- 添加數據
mysql> insert into student (name) values ('張三'), ('王五');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 張三 |   18 |
|  2 | 王五 |   18 |
+----+------+------+
2 rows in set (0.01 sec)

有指定值就用指定值填充

mysql> insert into student (name, age) values ('趙六', 20);
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 張三 |   18 |
|  2 | 王五 |   18 |
|  3 | 趙六 |   20 |
+----+------+------+
3 rows in set (0.00 sec)

5. UNIQUE 唯一約束

指定了唯一約束的列，該列的值在所有記錄中不能重復，比如一個人的身份證號，學生的學號等。

重構學生表，新增學號列

mysql> drop table if exists student;
Query OK, 0 rows affected (0.05 sec)mysql> create table student(-> id bigint,-> name varchar(20),-> sno varchar(10)-> );
Query OK, 0 rows affected (0.06 sec)

** 不設置唯一約束時，學號可以重復**

mysql> insert into student (id, name, sno) values (1, '張三', '123456789');
Query OK, 1 row affected (0.00 sec)mysql> insert into student (id, name, sno) values (1, '張三', '123456789');
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+------+------+-----------+
| id   | name | sno       |
+------+------+-----------+
|    1 | 張三 | 123456789 |
|    1 | 張三 | 123456789 |
+------+------+-----------+
2 rows in set (0.00 sec)

** 重構學生表，為學號列設置唯一約束**

mysql> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)mysql> create table student(-> id bigint,-> name varchar(20),-> sno varchar(10) UNIQUE-> );
Query OK, 0 rows affected (0.09 sec)mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sno   | varchar(10) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入重復的學號時會報錯，這就是唯一約束在生效

mysql> insert into student (id, name, sno) values (1, '張三', '123456789');
Query OK, 1 row affected (0.03 sec)mysql> insert into student (id, name, sno) values (1, '張三', '123456789');
ERROR 1062 (23000): Duplicate entry '123456789' for key 'student.sno'

加入唯一約束的列可以寫入null值，且可以寫入多個null值

mysql> insert into student (id, name, sno) values (1, '張三', null);
Query OK, 1 row affected (0.03 sec)mysql> insert into student (id, name, sno) values (1, '張三', null);
Query OK, 1 row affected (0.03 sec)mysql> insert into student (id, name, sno) values (1, '張三', null);
Query OK, 1 row affected (0.03 sec)mysql> select * from student;
+------+------+-----------+
| id   | name | sno       |
+------+------+-----------+
|    1 | 張三 | 123456789 |
|    1 | 張三 | NULL      |
|    1 | 張三 | NULL      |
|    1 | 張三 | NULL      |
+------+------+-----------+
4 rows in set (0.00 sec)

6. PRIMARY KEY 主鍵約束

主鍵約束唯一標識數據庫表中的每條記錄（數據庫管理數據時，使用主鍵列作為數據行的“身份證編號”）。
主鍵必須包含唯一的值，且不能包含null值（非空約束 + 唯一約束）。
每個表只能有一個主鍵，可以由單個列或多個列組成（主鍵由多個列組成的是復合主鍵）。
通常每張表都只當一個主鍵，主鍵列建議使用bigint類型（范圍足夠大，不會溢出）。

重構學生表，為ID列添加非空和唯一約束

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)mysql> create table student(-> id bigint not null unique,-> name varchar(20) not null-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

在id列上加了非空約束和唯一約束，在表結構上的key那一列顯示PRI。

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)mysql> create table student(-> id bigint primary key,-> name varchar(20) not null-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

為id列加上主鍵約束，可以看到在表結構的key那一列顯示PRI

非空約束 + 唯一約束 = 主鍵約束

6.1 自增主鍵

把表中的ID字段設置成自增主鍵

mysql> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)mysql> create table student(-> id bigint primary key auto_increment,-> name varchar(20) not null-> );
Query OK, 0 rows affected (0.04 sec)mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

auto_increment是自增的關鍵字，一個表中只能由一個列是自增列。
可以在表結構的Extra列上看到auto_increment。

可以手動指定主鍵列的值，但是要保證不重復

mysql> insert into student (id, name) values (1, '張三');
Query OK, 1 row affected (0.01 sec)mysql> insert into student (id, name) values (1, '李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'student.PRIMARY'
mysql> insert into student (id, name) values (2, '李四');
Query OK, 1 row affected (0.03 sec)

主鍵列在手動設置時，如果設置為null,則會使用自增

mysql> insert into student (id, name) values (null, '王五');
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | 張三 |
|  2 | 李四 |
|  3 | 王五 |
+----+------+
3 rows in set (0.01 sec)

插入除了主鍵之外的所有非空列（推薦使用這種方法插入數據）

mysql> insert into student (name) values ('趙六');
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | 張三 |
|  2 | 李四 |
|  3 | 王五 |
|  4 | 趙六 |
+----+------+
4 rows in set (0.01 sec)

自定義的主鍵值，只要滿足非空和唯一即可，不需要嚴格按照數字遞增

mysql> insert into student (id, name) values (100, '哈哈');
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+------+
| id  | name |
+-----+------+
|   1 | 張三 |
|   2 | 李四 |
|   3 | 王五 |
|   4 | 趙六 |
| 100 | 哈哈 |
+-----+------+
5 rows in set (0.00 sec)mysql> insert into student (id, name) values (99, '哈哈');
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+------+
| id  | name |
+-----+------+
|   1 | 張三 |
|   2 | 李四 |
|   3 | 王五 |
|   4 | 趙六 |
|  99 | 哈哈 |
| 100 | 哈哈 |
+-----+------+
6 rows in set (0.00 sec)mysql> insert into student (name) values ('哈哈2');
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+-------+
| id  | name  |
+-----+-------+
|   1 | 張三  |
|   2 | 李四  |
|   3 | 王五  |
|   4 | 趙六  |
|  99 | 哈哈  |
| 100 | 哈哈  |
| 101 | 哈哈2 |
+-----+-------+
7 rows in set (0.00 sec)

6.1 一個自增主鍵包含多個列

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)mysql> create table student(-> id bigint,-> name varchar(20),-> primary key (id, name)-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果定義的主鍵包含多個列，那么表示的是多個列的值用-連起來的值，不能重復

mysql> insert into student (id, name) values (1, '張三');
Query OK, 1 row affected (0.01 sec)mysql> insert into student (id, name) values (2, '張三');
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | 張三 |
|  2 | 張三 |
+----+------+
2 rows in set (0.00 sec)mysql> insert into student (id, name) values (2, '張三');
ERROR 1062 (23000): Duplicate entry '2-張三' for key 'student.PRIMARY'

7. FOREIGN KEY 外鍵約束

外鍵約束用于定義主表和從表之間的關系。
外鍵約束定義在從表的列上，主表關聯的列必須是主鍵或唯一約束。
當定義外鍵后，要求從表中的外鍵列數據必須在主表的主鍵或唯一列存在或為null.

外鍵約束也是對數據的一個校驗過程，從表中使用主表中的某個值，這個值必須在主表中存在。

在這里插入圖片描述

語法

foreign key (class_id) references class(id)

創建班級表（主表），并初始化數據

mysql> drop table if exists class;
Query OK, 0 rows affected, 1 warning (0.04 sec)mysql> create table class(-> id bigint primary key auto_increment,-> name varchar(20) not null-> );
Query OK, 0 rows affected (0.05 sec)mysql> insert into class (name) values ('java01'), ('java02'), ('java03'), ('C++01'), ('C++02');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
|  1 | java01 |
|  2 | java02 |
|  3 | java03 |
|  4 | C++01  |
|  5 | C++02  |
+----+--------+
5 rows in set (0.02 sec

創建學生表，加入外鍵約束

mysql> drop table if exists student;
Query OK, 0 rows affected (0.03 sec)mysql> create table student(-> id bigint primary key auto_increment,-> name varchar(20) not null,-> sno varchar(20) unique,-> class_id bigint not null,-> foreign key (class_id) references class (id)-> );
Query OK, 0 rows affected (0.04 sec)mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | NO   |     | NULL    |                |
| sno      | varchar(20) | YES  | UNI | NULL    |                |
| class_id | bigint      | NO   | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

在這里插入圖片描述

插入一個班級編號在主表中不存在的一組數據

mysql> insert into student(name, sno, class_id) values ('qianqi', '1007', 6);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`java114`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

刪除主表中的數據

mysql> select * from student;
+----+------+-------+----------+
| id | name | sno   | class_id |
+----+------+-------+----------+
|  5 | 張三 | 10001 |        1 |
|  6 | 王五 | 10002 |        3 |
|  7 | 趙六 | 10003 |        5 |
+----+------+-------+----------+
3 rows in set (0.00 sec)mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
|  1 | java01 |
|  3 | java03 |
|  4 | C++01  |
|  5 | C++02  |
+----+--------+
4 rows in set (0.00 sec)mysql> delete from class where id = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`java114`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

刪除主表中的數據時，如果從表中有對主表的引用，則不允許刪除主表的記錄。

mysql> select * from student;
+----+------+-------+----------+
| id | name | sno   | class_id |
+----+------+-------+----------+
|  5 | 張三 | 10001 |        1 |
|  6 | 王五 | 10002 |        3 |
|  7 | 趙六 | 10003 |        5 |
+----+------+-------+----------+
3 rows in set (0.00 sec)mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
|  1 | java01 |
|  3 | java03 |
|  4 | C++01  |
|  5 | C++02  |
+----+--------+
4 rows in set (0.00 sec)mysql> delete from student where class_id = 3;
Query OK, 1 row affected (0.01 sec)mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
|  1 | java01 |
|  3 | java03 |
|  4 | C++01  |
|  5 | C++02  |
+----+--------+
4 rows in set (0.00 sec)mysql> select * from student;
+----+------+-------+----------+
| id | name | sno   | class_id |
+----+------+-------+----------+
|  5 | 張三 | 10001 |        1 |
|  7 | 趙六 | 10003 |        5 |
+----+------+-------+----------+
2 rows in set (0.00 sec)

如果要刪除主表中的數據，必須先把從表中對主表的引用記錄刪除掉。

mysql> drop table class;
ERROR 3730 (HY000): Cannot drop table 'class' referenced by a foreign key constraint 'student_ibfk_1' on table 'student'.

刪除主表時，必須先解除主外鍵關系或者先刪除從表。

每當在有主外鍵約束的表中新增一條數據時，數據庫都會為我們做校驗，數據量非常大的時候，會嚴重影響數據庫的效率，在真正的工作中，一般不會為表加主外鍵約束。

數據庫的校驗操作一般是在應用程序層面處理好，再把正確的數據直接寫到數據庫中。

8. CHECK 約束

可以應用于一個或多個列，用于限制列中可接收的數據值，從而保證數據的完整性和準確性。
在8.0.16開始全面支持CHECK約束，之前的版本會忽略CHECK約束。

但是在工作中，一般是在應用程序級別進行校驗的。

重構學生表，有以下要求，年齡不能小于16歲，性別只能是男或女

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)mysql> create table student(-> id bigint primary key auto_increment,-> name varchar(20) not null,-> age int default 18,-> gender char(1),-> check(age >= 16),-> check(gender = '男' or gender = '女')-> );
Query OK, 0 rows affected (0.03 sec)mysql> desc student;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | bigint      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | NO   |     | NULL    |                |
| age    | int         | YES  |     | 18      |                |
| gender | char(1)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

符合check條件的插入

mysql> insert into student (name, age, gender) values ('張三', 18, '男');
Query OK, 1 row affected (0.03 sec)mysql> select * from student;
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  1 | 張三 |   18 | 男     |
+----+------+------+--------+
1 row in set (0.00 sec)

不符合check條件（age）的插入

mysql> insert into student (name, age, gender) values ('張三', 15, '男');
ERROR 3819 (HY000): Check constraint 'student_chk_1' is violated.

不符合check條件（gender）的插入

insert into student (name, age, gender) values ('王五', 18, '好');
ERROR 3819 (HY000): Check constraint 'student_chk_2' is violated.

** 都不符合**

mysql> insert into student (name, age, gender) values ('趙六', 8, '好');
ERROR 3819 (HY000): Check constraint 'student_chk_1' is violated.

【MySQL】-- 數據庫約束

文章目錄

1. 什么是數據庫約束

2. 約束類型

3. NOT NULL 非空約束

4. DEFALUT 默認值約束

5. UNIQUE 唯一約束

6. PRIMARY KEY 主鍵約束

6.1 自增主鍵

6.1 一個自增主鍵包含多個列

7. FOREIGN KEY 外鍵約束

8. CHECK 約束

相關文章

鴻蒙NEXT開發動畫案例2

團隊協作的潤滑劑——GitHub與協作流程

「Mac暢玩AIGC與多模態13」開發篇09 - 基于多插件協同開發智能體應用（天氣+名言查詢助手）

react-12父子組件間的數據傳遞（子傳父）（父傳子）- props實現

Spring Boot 單元測試使用教程（僅供參考）

React Hooks速成

k8s node 內存碎片化如何優化？

目標檢測（Object Detection）研究方向常用數據集簡單介紹

即開即用，封裝 Flask 項目為 exe 文件實操步驟

uniapp開發微信小程序時如何進行分包（新手圖文）

基于C++的IOT網關和平臺2：github項目ctGateway技術說明書

從巴別塔到通天塔：Manus AI 如何重構多語言手寫識別的智能版圖

n8n 快速入門2：構建自動化工作流

從實列中學習linux shell10 ：如何根據服務器的內存，cpu 以及 ssd硬盤來確定mysql 的最大并發數

數據結構--AVL樹

泰迪杯特等獎案例學習資料：基于多模態融合與邊緣計算的智能溫室環境調控系統

動手學深度學習12.1. 編譯器和解釋器-筆記練習（PyTorch）

[java八股文][Java并發編程面試篇]并發安全

VMware搭建ubuntu保姆級教程

馮諾依曼結構與哈佛架構深度解析