MySQL數(shù)據(jù)庫的約束使用實(shí)例

更新時(shí)間：2022年12月03日 15:12:27 作者：敲代碼の流川楓

約束是用來限制表中的數(shù)據(jù)長什么樣子的,即什么樣的數(shù)據(jù)可以插入到表中,什么樣的數(shù)據(jù)插入不到表中,下面這篇文章主要給大家介紹了關(guān)于如何通過一文理解MySQL數(shù)據(jù)庫的約束與表的設(shè)計(jì)的相關(guān)資料,需要的朋友可以參考下

1. NULL約束

創(chuàng)建表時(shí),可以指定某列不為空

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(唯一約束)

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

重新設(shè)置一下表的結(jié)構(gòu)

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這列的值時(shí)唯一的不重復(fù)的

3. DEFAULT(默認(rèn)值約束)

規(guī)定沒有給列賦值時(shí)的默認(rèn)值

指定插入數(shù)據(jù)時(shí)，name列為空，默認(rèn)值unkown

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)

插入一個(gè)id但不插入name時(shí):

insert into student2 (id) values (1);
mysql> select*from student2;
+----+------+--------+---------+
| id | sn   | name   | qq_mail |
+----+------+--------+---------+
|  1 | NULL | unkown | NULL    |
+----+------+--------+---------+
1 row in set (0.00 sec)

name是默認(rèn)值

4. PRIMARY KEY(主鍵約束)

NOT NULL 和 UNIQUE 的結(jié)合

確保某列有唯一標(biāo)識,有助于快速找到表中的一個(gè)特定記錄

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 的結(jié)合,也可以不用 not null

id int primary key

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

當(dāng)插入數(shù)據(jù)對應(yīng)字段不給定值時(shí),使用最大值加一

mysql> create table student4(
    id int primary key auto_increment,
    sn int unique,
    name varchar(20) default 'unkown',
    qq_mail varchar(20)
);

接下來我們插入記錄時(shí),id為null

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);
結(jié)果
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)

可以看出,使用自增長之后,插入數(shù)據(jù)給值時(shí),用給定的值插入,插入數(shù)據(jù)不給值時(shí),默認(rèn)插入最近一次的最大值+1

5. FOREIGN KEY(外鍵約束)

保證一個(gè)表中的數(shù)據(jù)匹配另一個(gè)表中的值的參照完整性

創(chuàng)建一個(gè)班級表class,id為主鍵.

創(chuàng)建學(xué)生表student，一個(gè)學(xué)生對應(yīng)一個(gè)班級，一個(gè)班級對應(yīng)多個(gè)學(xué)生。使用id為主鍵， classes_id為外鍵，關(guān)聯(lián)班級表id

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)

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)

此時(shí)學(xué)生表就和班級表關(guān)聯(lián)起來了

外鍵約束是兩個(gè)表之間的相互約束

我們插入一個(gè)非法數(shù)據(jù),現(xiàn)在class的id列還是空的,插任何數(shù)據(jù)都會不合法

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`))

插入失敗,因?yàn)閏lass的id列沒有10這個(gè)數(shù)據(jù)

說明學(xué)生表中的數(shù)據(jù)依賴于班級表的數(shù)據(jù),班級表的數(shù)據(jù)對學(xué)生表的數(shù)據(jù)產(chǎn)生約束力,班級表為父表,學(xué)生表為子表

我們先給班級表和學(xué)生表插入數(shù)據(jù)

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)

因?yàn)閿?shù)據(jù)都是合法的,所以成功插入

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

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`))

當(dāng)我們刪除班級表中的數(shù)據(jù)時(shí),發(fā)現(xiàn)刪除失敗

原因是班級表的數(shù)據(jù)被學(xué)生表給引用了,如果刪除成功,那么子表的數(shù)據(jù)就沒有意義了

因此父表的數(shù)據(jù)被引用時(shí),是不能刪除的,如果要?jiǎng)h除,就先刪除子表后刪除父表

到此這篇關(guān)于MySQL數(shù)據(jù)庫的約束使用實(shí)例的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫的約束內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: