當前位置：首頁 > 數據庫 > Mysql > 詳細頁面

MySQL數據庫的約束使用實例

時間：2022-12-06來源：www.caymanbraccottage.com作者：電腦系統城

1. NULL約束
2. UNIQUE(唯一約束)
3. DEFAULT(默認值約束)
4. PRIMARY KEY(主鍵約束)
5. FOREIGN KEY(外鍵約束)

數據庫的約束就是關系型數據庫給我們提供的一種"校驗數據"合法性的機制

1. NULL約束

創建表時,可以指定某列不為空

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 create table student(
id int not null,
sn int,
name varchar(20),
qq_mail varchar(20)
);
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| sn | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| qq_mail | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2. UNIQUE(唯一約束)

保證某列的每行必須有唯一的值

重新設置一下表的結構

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 create table student1(
id int not null,
sn int unique,
name varchar(20),
qq_mail varchar(20)
);
mysql> desc student1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| sn | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
| qq_mail | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

表示sn這列的值時唯一的不重復的

3. DEFAULT(默認值約束)

規定沒有給列賦值時的默認值

指定插入數據時，name列為空，默認值unkown

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 create table student2(
id int not null,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(20)
);
mysql> desc student2;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| sn | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | unkown | |
| qq_mail | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

插入一個id但不插入name時:

name是默認值

4. PRIMARY KEY(主鍵約束)

NOT NULL 和 UNIQUE 的結合

確保某列有唯一標識,有助于快速找到表中的一個特定記錄

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 create table student3(
id int not null primary key,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(20)
);
mysql> desc student3;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| sn | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | unkown | |
| qq_mail | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

主鍵是not null 和 unique 的結合,也可以不用 not null

id int primary key

對于整數類型的主鍵,例如id,常搭配自增長auto_increment來使用

當插入數據對應字段不給定值時,使用最大值加一

1
2
3
4
5
6 mysql> create table student4(
id int primary key auto_increment,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(20)
);

接下來我們插入記錄時,id為null

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 mysql> insert into student4 values (null,1000,'zhangsan',null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student4 values (null,1001,'zhangsi',null);
Query OK, 1 row affected (0.00 sec)
//將id置為100
insert into student4 values(100,1002,'zhangwu',null);
//將id置為null
insert into student4 values(null,1003,'zhangliu',null);
結果
mysql> select *from student4;
+-----+------+----------+---------+
| id | sn | name | qq_mail |
+-----+------+----------+---------+
| 1 | 1000 | zhangsan | NULL |
| 2 | 1001 | zhangsi | NULL |
| 100 | 1002 | zhangwu | NULL |
| 101 | 1003 | zhangliu | NULL |
+-----+------+----------+---------+
4 rows in set (0.00 sec)

可以看出,使用自增長之后,插入數據給值時,用給定的值插入,插入數據不給值時,默認插入最近一次的最大值+1

5. FOREIGN KEY(外鍵約束)

保證一個表中的數據匹配另一個表中的值的參照完整性

創建一個班級表class,id為主鍵.

創建學生表student，一個學生對應一個班級，一個班級對應多個學生。使用id為主鍵， classes_id為外鍵，關聯班級表id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 create table class(
id int primary key,
name varchar(20),
`desc` VARCHAR(100)
);
Query OK, 0 rows affected (0.03 sec)
mysql> desc class;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| desc | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 create table student5(
id int primary key auto_increment,
sn int unique,name varchar(20) default 'unknow',
qq_mail varchar(20),
class_id int,
foreign key(class_id) references class(id)
);
Query OK, 0 rows affected (0.03 sec)
mysql> desc student5;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sn | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | unknow | |
| qq_mail | varchar(20) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

此時學生表就和班級表關聯起來了

外鍵約束是兩個表之間的相互約束

我們插入一個非法數據,現在class的id列還是空的,插任何數據都會不合法

1
2
3
4
5 insert into student5 values(1,1001,'張三',null,10);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`java_3`.`student5`,
CONSTRAINT `student5_ibfk_1` FOREIGN KEY (`class_id`)
REFERENCES `class` (`id`))

插入失敗,因為class的id列沒有10這個數據

說明學生表中的數據依賴于班級表的數據,班級表的數據對學生表的數據產生約束力,班級表為父表,學生表為子表

我們先給班級表和學生表插入數據

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 mysql> insert into class values(1,'java001',null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student5 values(1,1001,'張三',null,1);
Query OK, 1 row affected (0.00 sec)
mysql> select*from class;
+----+---------+------+
| id | name | desc |
+----+---------+------+
| 1 | java001 | NULL |
+----+---------+------+
1 row in set (0.00 sec)
mysql> select*from student5;
+----+------+------+---------+----------+
| id | sn | name | qq_mail | class_id |
+----+------+------+---------+----------+
| 1 | 1001 | 張三 | NULL | 1 |
+----+------+------+---------+----------+
1 row in set (0.00 sec)

因為數據都是合法的,所以成功插入

下面舉例說明子表也會反過來約束父表

1
2
3
4
5
6 mysql> delete from class where id = 1;
ERROR 1451 (23000):
Cannot delete or update a parent row:
a foreign key constraint fails (`java_3`.`student5`,
CONSTRAINT `student5_ibfk_1` FOREIGN KEY (`class_id`)
REFERENCES `class` (`id`))