MySQL | 尚硅谷 | 第13章

MySQL筆記：第13章_約束

文章目錄

MySQL筆記：第13章_約束
- - - 第13章_約束
  - 1. 約束(constraint)概述
  - - - 1.1 為什么需要約束
      - 1.2 什么是約束
      - 1.3 約束的分類
      - 演示代碼
  - 2. 非空約束
  - - - 2.1 作用
      - 2.2 關鍵字
      - 2.3 特點
      - 2.4 添加非空約束
      - 2.5 刪除非空約束
      - 演示代碼
  - 3. 唯一性約束
  - - - 3.1 作用
      - 3.2 關鍵字
      - 3.3 特點
      - 3.4 添加唯一約束
      - 3.5 關于復合唯一約束
      - 3.5 刪除唯一約束
      - 演示代碼
  - 4. PRIMARY KEY 約束
  - - - 4.1 作用
      - 4.2 關鍵字
      - 4.3 特點
      - 4.4 添加[主鍵約束](https://so.csdn.net/so/search?q=主鍵約束&spm=1001.2101.3001.7020)
      - 4.5 關于復合主鍵
      - 4.6 刪除主鍵約束
      - 演示代碼
  - 5. 自增列：AUTO_INCREMENT
  - - - 5.1 作用
      - 5.2 關鍵字
      - 5.3 特點和要求
      - 5.4 如何指定自增約束
      - 5.5 如何刪除自增約束
      - 5.6 MySQL 8.0新特性—自增變量的持久化
      - 演示代碼
  - 6. FOREIGN KEY 約束
  - - - 6.1 作用
      - 6.2 關鍵字
      - 6.3 主表和從表/父表和子表
      - 6.4 特點
      - 6.5 添加外鍵約束
      - 6.6 演示問題
      - 6.7 約束等級
      - 6.8 刪除外鍵約束
      - 6.9 開發場景
      - 6.10 阿里開發規范
      - 演示代碼
  - 7. CHECK 約束
  - - - 7.1 作用
      - 7.2 關鍵字
      - 7.3 說明：MySQL 5.7 不支持
      - 演示代碼
  - 8. DEFAULT約束
  - - - 8.1 作用
      - 8.2 關鍵字
      - 8.3 如何給字段加默認值
      - 8.4 如何刪除默認值約束
      - 演示代碼
  - 9. 面試
  - 課后練習

第13章_約束

1. 約束(constraint)概述

1.1 為什么需要約束

數據完整性（Data Integrity）是指數據的精確性（Accuracy）和可靠性（Reliability）。它是防止數據庫中存在不符合語義規定的數據和防止因錯誤信息的輸入輸出造成無效操作或錯誤信息而提出的

為了保證數據的完整性，SQL規范以約束的方式對表數據進行額外的條件限制。從以下四個方面考慮：

實體完整性（Entity Integrity）：例如，同一個表中，不能存在兩條完全相同無法區分的記錄
域完整性（Domain Integrity）：例如：年齡范圍0-120，性別范圍“男/女”
引用完整性（Referential Integrity）：例如：員工所在部門，在部門表中要能找到這個部門用戶
自定義完整性（User-defined Integrity）：例如：用戶名唯一、密碼不能為空等，本部門經理的工資不得高于本部門職工的平均工資的5倍。

1.2 什么是約束

約束是表級的強制規定。

可以在創建表時規定約束（通過 CREATE TABLE 語句），或者在表創建之后通過 ALTER TABLE 語句規定約束。

1.3 約束的分類

根據約束數據列的限制，約束可分為：

單列約束：每個約束只約束一列
多列約束：每個約束可約束多列數據

根據約束的作用范圍，約束可分為：

列級約束：只能作用在一個列上，跟在列的定義后面
表級約束：可以作用在多個列上，不與列一起，而是單獨定義

.	位置	支持的約束類型	是否可以起約束名
列級約束	列的后面	語法都支持，但外鍵沒有效果	不可以
表級約束	所有列的下面	默認和非空不支持，其他支持	可以（主鍵沒有效果）

根據約束起的作用，約束可分為：

NOT NULL 非空約束，規定某個字段不能為空
UNIQUE 唯一約束，規定某個字段在整個表中是唯一的
PRIMARY KEY 主鍵(非空且唯一)約束
FOREIGN KEY 外鍵約束
CHECK 檢查約束
DEFAULT 默認值約束

注意： MySQL不支持check約束，但可以使用check約束，而沒有任何效果

查看某個表已有的約束

#information_schema數據庫名（系統庫）
#table_constraints表名稱（專門存儲各個表的約束）
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';

演示代碼

/*
1. 基礎知識
1.1 為什么需要約束？ 為了保證數據的完整性！1.2 什么叫約束？對表中字段的限制。1.3 約束的分類：角度1：約束的字段的個數
單列約束 vs 多列約束角度2：約束的作用范圍列級約束：將此約束聲明在對應字段的后面
表級約束：在表中所有字段都聲明完，在所有字段的后面聲明的約束角度3：約束的作用（或功能）① not null (非空約束)
② unique  (唯一性約束)
③ primary key (主鍵約束)
④ foreign key (外鍵約束)
⑤ check (檢查約束)
⑥ default (默認值約束)1.4 如何添加/刪除約束？CREATE TABLE時添加約束ALTER TABLE 時增加約束、刪除約束*/#2. 如何查看表中的約束
SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'employees';
/*
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | atguigudb         | emp_email_uk    | atguigudb    | employees  | UNIQUE          | YES      |
| def                | atguigudb         | emp_emp_id_pk   | atguigudb    | employees  | UNIQUE          | YES      |
| def                | atguigudb         | PRIMARY         | atguigudb    | employees  | PRIMARY KEY     | YES      |
| def                | atguigudb         | emp_dept_fk     | atguigudb    | employees  | FOREIGN KEY     | YES      |
| def                | atguigudb         | emp_job_fk      | atguigudb    | employees  | FOREIGN KEY     | YES      |
| def                | atguigudb         | emp_manager_fk  | atguigudb    | employees  | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
*/

2. 非空約束

2.1 作用

限定某個字段/某列的值不允許為空

2.2 關鍵字

NOT NULL

2.3 特點

默認，所有的類型的值都可以是NULL，包括INT、FLOAT等數據類型
非空約束只能出現在表對象的列上，只能某個列單獨限定非空，不能組合非空
一個表可以有很多列都分別限定了非空
空字符串’'不等于NULL，0也不等于NULL

2.4 添加非空約束

（1）建表時

CREATE TABLE 表名稱(
字段名	數據類型,
字段名	數據類型 NOT NULL,
字段名	數據類型 NOT NULL
);

舉例：

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);CREATE TABLE student(
sid int,
sname varchar(20) not null,
tel char(11) ,
cardid char(18) not null
);insert into student values(1,'張三','13710011002','110222198912032545'); #成功insert into student values(2,'李四','13710011002',null);#身份證號為空 ERROR 1048 (23000): Column 'cardid' cannot be nullinsert into student values(2,'李四',null,'110222198912032546');#成功，tel允許為空insert into student values(3,null,null,'110222198912032547');#失敗 ERROR 1048 (23000): Column 'sname' cannot be null

（2）建表后

alter table 表名稱 modify 字段名 數據類型 not null;

舉例：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;
alter table student modify sname varchar(20) not null;

2.5 刪除非空約束

alter table 表名稱 modify 字段名 數據類型 NULL;
#去掉not null，相當于修改某個非注解字段，該字段允許為空

或

alter table 表名稱 modify 字段名 數據類型;
#去掉not null，相當于修改某個非注解字段，該字段允許為空

舉例：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

演示代碼

CREATE DATABASE dbtest13;
USE dbtest13;
#2. not null (非空約束)
#限定某個字段/某列的值不允許為空
#3.1 在CREATE TABLE時添加約束CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2));DESC test1;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | NO   |     | NULL    |       |
| last_name | varchar(15)   | NO   |     | NULL    |       |
| email     | varchar(25)   | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/INSERT INTO test1(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',3400);#錯誤：Column 'last_name' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(2,NULL,'tom1@126.com',3400);#錯誤：Column 'id' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(NULL,'Jerry','jerry@126.com',3400);
#錯誤：Field 'last_name' doesn't have a default value
INSERT INTO test1(id,email)
VALUES(2,'abc@126.com');#錯誤：Column 'last_name' cannot be null
UPDATE test1
SET last_name = NULL
WHERE id = 1;UPDATE test1
SET email = 'tom@126.com'
WHERE id = 1;
#結論：約束的存在會對增刪改等操作產生影響#3.2 在ALTER TABLE時添加約束
SELECT * FROM test1;
/*
+----+-----------+-------------+---------+
| id | last_name | email       | salary  |
+----+-----------+-------------+---------+
|  1 | Tom       | tom@126.com | 3400.00 |
+----+-----------+-------------+---------+
*/
DESC test1;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | NO   |     | NULL    |       |
| last_name | varchar(15)   | NO   |     | NULL    |       |
| email     | varchar(25)   | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | NO   |     | NULL    |       |
| last_name | varchar(15)   | NO   |     | NULL    |       |
| email     | varchar(25)   | NO   |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/#3.3 在ALTER TABLE時刪除約束
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889

3. 唯一性約束

3.1 作用

用來限制某個字段/某列的值不能重復。

3.2 關鍵字

UNIQUE

3.3 特點

同一個表可以有多個唯一約束。
唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一。
唯一性約束允許列值為空。
在創建唯一約束的時候，如果不給唯一約束命名，就默認和列名相同。
MySQL會給唯一約束的列上默認創建一個唯一索引。

3.4 添加唯一約束

（1）建表時

create table 表名稱(
字段名	數據類型,
字段名	數據類型 unique,
字段名	數據類型 unique key,
字段名 數據類型
);create table 表名稱(
字段名	數據類型,
字段名	數據類型,
字段名	數據類型,
[constraint 約束名] unique key(字段名)
);

舉例：

create table student(
sid int,
sname varchar(20),
tel char(11) unique,
cardid char(18) unique key
);CREATE TABLE t_course(
cid INT UNIQUE,
cname VARCHAR(100) UNIQUE,
description VARCHAR(200)
);CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
--	使用表級約束語法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

表示用戶名和密碼組合不能重復

insert into student values(1,'張三','13710011002','101223199012015623'); insert into student values(2,'李四','13710011003','101223199012015624');select *from student;	
/*
+	-----	+-------	+-------------	+--------------------	+
| sid |	sname |	tel	| cardid	|
+	-----	+-------	+-------------	+--------------------	+
|	1	|	張三	| 13710011002	| 101223199012015623 |
|	2	|	李四	| 13710011003	| 101223199012015624 |
+	-----	+-------	+-------------	+--------------------	+
*/insert into student values(3,'王五','13710011004','101223199012015624'); #身份證號重復 ERROR 1062 (23000): Duplicate entry '101223199012015624' for key 'cardid'insert into student values(3,'王五','13710011003','101223199012015625'); ERROR 1062 (23000): Duplicate entry '13710011003' for key 'tel'

（2）建表后指定唯一鍵約束

#字段列表中如果是一個字段，表示該列的值唯一。
#如果是兩個或更多個字段，那么復合唯一，
#即多個字段的組合是唯一的
#方式1：
alter table 表名稱 add unique key(字段列表);#方式2：
alter table 表名稱 modify 字段名 字段類型 unique;

舉例：

ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);ALTER TABLE USER
MODIFY NAME VARCHAR(20) UNIQUE;

舉例：

create table student(
sid int primary key,
sname varchar(20),
tel char(11) ,
cardid char(18)
);alter table student add unique key(tel);
alter table student add unique key(cardid);

3.5 關于復合唯一約束

create table 表名稱(
字段名	數據類型,
字段名	數據類型,
字段名	數據類型,
unique key(字段列表) 
#字段列表中寫的是多個字段名，多個字段名用逗號分隔表示
#那么是復合唯一，即多個字段的組合是唯一的
);#學生表
create table student(
sid int,	#學號
sname varchar(20),	#姓名
tel char(11) unique key,	#電話
cardid char(18) unique key #身份證號
);#課程表
create table course(
cid int,	#課程編號
cname varchar(20)	#課程名稱
);#選課表
create table student_course(
id int,
sid int,
cid int,
score int,
unique key(sid,cid)	#復合唯一
);insert into student values(1,'張三','13710011002','101223199012015623');#成功 insert into student values(2,'李四','13710011003','101223199012015624');#成功 insert into course values(1001,'Java'),(1002,'MySQL');#成功select * from student;/*
+	-----	+-------		+-------------	+--------------------	+
| sid |	sname | tel	| cardid	|
+	-----	+-------	+-------------	+--------------------	+
|	1 |	張三	| 13710011002 | 101223199012015623 |
|	2 |	李四	| 13710011003 | 101223199012015624 |
+	-----	+-------	+-------------	+--------------------	+
*/	
select * from course;	
/*	
+	------	+	-------	+		
| cid	|	cname |		
+	------	+	-------	+		
| 1001	|	Java	|		
| 1002	|	MySQL |		
+	------	+	-------	+		
*/					
insert into student_course values
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);#成功select	* from	student_course;
/*
+	----	+------	+------	+-------	+
| id |	sid	|	cid	|	score	|
+	----	+------	+------	+-------	+
|	1	|	1	|	1001	|	89	|
|	2	|	1	|	1002	|	90	|
|	3	|	2	|	1001	|	88	|
|	4	|	2	|	1002	|	56	|
+	----	+------	+------	+-------	+
*/
insert	into student_course values (5, 1, 1001, 88);#失敗
#ERROR 1062 (23000): Duplicate entry '1-1001' for key 'sid'	違反sid-cid的復合唯一

3.5 刪除唯一約束

添加唯一性約束的列上也會自動創建唯一索引
刪除唯一約束只能通過刪除唯一索引的方式刪除
刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣
如果創建唯一約束時未指定名稱，如果是單列，就默認和列名相同；如果是組合列，那么默認和()中排在第一個的列名相同。也可以自定義唯一性約束名

#查看都有哪些約束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';ALTER TABLE USER
DROP INDEX uk_name_pwd;

注意：可以通過 show index from 表名稱; 查看表的索引

演示代碼

#3. unique  (唯一性約束)
#用來限制某個字段/某列的值不能重復。
#3.1 在CREATE TABLE時添加約束
CREATE TABLE test2(
id INT UNIQUE, #列級約束
last_name VARCHAR(15) ,
email VARCHAR(25),
salary DECIMAL(10,2),
#表級約束
CONSTRAINT uk_test2_email UNIQUE(email)#uk_test2_email為email的約束名
);DESC test2;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  | UNI | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  | UNI | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/
SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'test2';
/*
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | dbtest13          | id              | dbtest13     | test2      | UNIQUE          | YES      |
| def                | dbtest13          | uk_test2_email  | dbtest13     | test2      | UNIQUE          | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
*/
#在創建唯一約束的時候，如果不給唯一約束命名，就默認和列名相同。
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',4500);#錯誤：Duplicate entry '1' for key 'test2.id'
INSERT INTO test2(id,last_name,email,salary)
VALUES(1,'Tom1','tom1@126.com',4600);#錯誤：Duplicate entry 'tom@126.com' for key 'test2.uk_test2_email'
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1','tom@126.com',4600);#可以向聲明為unique的字段上添加null值。而且可以多次添加null
INSERT INTO test2(id,last_name,email,salary)
VALUES(2,'Tom1',NULL,4600);INSERT INTO test2(id,last_name,email,salary)
VALUES(3,'Tom2',NULL,4600);SELECT * FROM test2;
/*
+------+-----------+-------------+---------+
| id   | last_name | email       | salary  |
+------+-----------+-------------+---------+
|    1 | Tom       | tom@126.com | 4500.00 |
|    2 | Tom1      | NULL        | 4600.00 |
|    3 | Tom2      | NULL        | 4600.00 |
+------+-----------+-------------+---------+
*/#3.2 在ALTER TABLE時添加約束
DESC test2;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  | UNI | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  | UNI | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/UPDATE test2
SET salary = 5000
WHERE id = 3;
#方式1：
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
#方式2：
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;
DESC test2;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  | UNI | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  | UNI | NULL    |       |
| salary    | decimal(10,2) | YES  | UNI | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/#3.3 復合的唯一性約束
CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),#表級約束
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)#`name` 和`password`不同時一樣即可
);INSERT INTO USER
VALUES(1,'Tom','abc');
#可以成功的：
INSERT INTO USER
VALUES(1,'Tom1','abc');SELECT *
FROM USER;
/*
+------+------+----------+
| id   | name | password |
+------+------+----------+
|    1 | Tom  | abc      |
|    1 | Tom1 | abc      |
+------+------+----------+
*/#案例：復合的唯一性約束的案例
#學生表
CREATE TABLE student(sid INT,	#學號sname VARCHAR(20),	#姓名tel CHAR(11) UNIQUE KEY,  #電話cardid CHAR(18) UNIQUE KEY #身份證號
);#課程表
CREATE TABLE course(cid INT,  #課程編號cname VARCHAR(20)     #課程名稱
);#選課表
CREATE TABLE student_course(id INT,sid INT,  #學號cid INT,  #課程編號score INT,UNIQUE KEY(sid,cid)  #復合唯一
);
INSERT INTO student VALUES(1,'張三','13710011002','101223199012015623');#成功
INSERT INTO student VALUES(2,'李四','13710011003','101223199012015624');#成功
INSERT INTO course VALUES(1001,'Java'),(1002,'MySQL');#成功SELECT * FROM student;
/*
+------+--------+-------------+--------------------+
| sid  | sname  | tel         | cardid             |
+------+--------+-------------+--------------------+
|    1 | 張三   | 13710011002 | 101223199012015623 |
|    2 | 李四   | 13710011003 | 101223199012015624 |
+------+--------+-------------+--------------------+
*/SELECT * FROM course;
/*
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | Java  |
| 1002 | MySQL |
+------+-------+
*/INSERT INTO student_course VALUES
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);#成功SELECT * FROM student_course;
/*
+------+------+------+-------+
| id   | sid  | cid  | score |
+------+------+------+-------+
|    1 |    1 | 1001 |    89 |
|    2 |    1 | 1002 |    90 |
|    3 |    2 | 1001 |    88 |
|    4 |    2 | 1002 |    56 |
+------+------+------+-------+
*/#錯誤：Duplicate entry '2-1002' for key 'student_course.sid'
INSERT INTO student_course VALUES
(5,2,1002,67);#3.4 刪除唯一性約束
-- 添加唯一性約束的列上也會自動創建唯一索引。
-- 刪除唯一約束只能通過刪除唯一索引的方式刪除。
-- 刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。
-- 如果創建唯一約束時未指定名稱，如果是單列，就默認和列名相同；如果是組合列，那么默認和()中排在第一個的列名相同。也可以自定義唯一性約束名。SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'student_course';SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'test2';DESC test2;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  | UNI | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  | UNI | NULL    |       |
| salary    | decimal(10,2) | YES  | UNI | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/
#如何刪除唯一性索引
ALTER TABLE test2
DROP INDEX last_name;ALTER TABLE test2
DROP INDEX uk_test2_sal;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  | UNI | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  | UNI | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235

4. PRIMARY KEY 約束

4.1 作用

用來唯一標識表中的一行記錄。

4.2 關鍵字

primary key

4.3 特點

主鍵約束相當于唯一約束+非空約束的組合，主鍵約束列不允許重復，也不允許出現空值
一個表最多只能有一個主鍵約束，建立主鍵約束可以在列級別創建，也可以在表級別上創建。
主鍵約束對應著表中的一列或者多列（復合主鍵）
如果是多列組合的復合主鍵約束，那么這些列都不允許為空值，并且組合的值不允許重復。
MySQL的主鍵名總是PRIMARY，就算自己命名了主鍵約束名也沒用。
當創建主鍵約束時，系統默認會在所在的列或列組合上建立對應的主鍵索引（能夠根據主鍵查詢的，就根據主鍵查詢，效率更高）。如果刪除主鍵約束了，主鍵約束對應的索引就自動刪除了。
需要注意的一點是，不要修改主鍵字段的值。因為主鍵是數據記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞數據的完整性。

4.4 添加主鍵約束

（1）建表時指定主鍵約束

create table 表名稱(
字段名	數據類型 primary key, #列級模式
字段名	數據類型,
字段名 數據類型
);create table 表名稱(
字段名	數據類型,
字段名	數據類型,
字段名	數據類型,
[constraint 約束名] primary key(字段名) #表級模式
);

舉例：

create table temp(
id int primary key,
name varchar(20)
);
desc temp;			
/*		
+	-------	+-------------	+------	+-----	+---------	+-------	+
| Field |	Type	| Null	| Key | Default	| Extra |
+	-------	+-------------	+------	+-----	+---------	+-------	+
| id	|	int(11)	| NO	| PRI | NULL	|	|
| name	|	varchar(20) | YES	|	| NULL	|	|
+	-------	+-------------	+------	+-----	+---------	+-------	+
*/		insert into temp values(1,'張三');#成功
insert into temp values(2,'李四');#成功
select * from temp;
/*
+	----	+------	+
| id |	name |
+	----	+------	+
|	1	|	張三 |
|	2	|	李四 |
+	----	+------	+
*/insert into temp values(1,'張三');#失敗
ERROR 1062 (23000): Duplicate（重復） entry（鍵入，輸入） '1' for key 'PRIMARY'insert into temp values(1,'王五');#失敗
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into temp values(3,'張三');#成功select * from temp;
/*
+	----	+------	+
| id |	name |
+	----	+------	+
|	1	|	張三 |
|	2	|	李四 |
|	3	|	張三 |
+	----	+------	+
*/
insert	into temp values(4,null);#成功insert into temp values(null,'李琦');#失敗 ERROR 1048 (23000): Column 'id' cannot be nullselect * from temp;
/*
+	----	+------	+
| id |	name |
+	----	+------	+
|	1	|	張三 |
|	2	|	李四 |
|	3	|	張三 |
|	4	|	NULL |
+	----	+------	+
*/#演示一個表建立兩個主鍵約束
create table temp(
id int primary key,
name varchar(20) primary key
);
ERROR 1068 (42000): Multiple（多重的） primary key defined（定義）

再舉例：

列級約束

CREATE TABLE emp4(
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20)
);

表級約束

CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

（2）建表后增加主鍵約束

ALTER TABLE 表名稱 ADD PRIMARY KEY(字段列表); 
#字段列表可以是一個字段，也可以是多個字段，如果是多個字段的話，是復合主鍵ALTER TABLE student ADD PRIMARY KEY (sid);ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

4.5 關于復合主鍵

create table 表名稱(
字段名	數據類型,
字段名	數據類型,
字段名	數據類型,
primary key(字段名1,字段名2)	#表示字段1和字段2的組合是唯一的，也可以有更多個字段
);#學生表
create table student(
sid int primary key,	#學號
sname varchar(20)	#學生姓名
);
#課程表
create table course(
cid int primary key,	#課程編號
cname varchar(20)	#課程名稱
);
#選課表
create table student_course(
sid int,
cid int,
score int,
primary key(sid,cid)	#復合主鍵
);insert into student values(1,'張三'),(2,'李四');
insert into course values(1001,'Java'),(1002,'MySQL');
select * from student;
/*
+	-----	+-------	+
| sid |	sname |
+	-----	+-------	+
|	1 |	張三	|
|	2 |	李四	|
+	-----	+-------	+
*/
select * from course;
/*
+	------	+	-------	+
| cid	|	cname |
+	------	+	-------	+
| 1001	|	Java	|
| 1002	|	MySQL |
+	------	+	-------	+
*/insert into student_course values(1, 1001, 89),(1,1002,90),(2,1001,88),(2,1002,56);select * from	student_course;/*
+	-----	+------	+-------	+
| sid |	cid	| score	|
+	-----	+------	+-------	+
|	1	|	1001	|	89	|
|	1	|	1002	|	90	|
|	2	|	1001	|	88	|
|	2	|	1002	|	56	|
+	-----	+------	+-------	+
*/insert into student_course values(1, 1001, 100);
ERROR 1062 (23000): Duplicate entry '1-1001' for key 'PRIMARY'desc student_course;		
/*		
+-------	+---------	+------	+-----	+---------	+-------	+
| Field |	Type	|	Null |	Key |	Default | Extra |
+-------	+---------	+------	+-----	+---------	+-------	+
| sid	|	int(11) |	NO	|	PRI |	NULL	|	|
| cid	|	int(11) |	NO	|	PRI |	NULL	|	|
| score |	int(11) |	YES	|	|	NULL	|	|
+-------	+---------	+------	+-----	+---------	+-------	+
*/

再舉例

CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);

4.6 刪除主鍵約束

alter table 表名稱 drop primary key;

舉例：

ALTER TABLE student DROP PRIMARY KEY;ALTER TABLE emp5 DROP PRIMARY KEY;

說明：刪除主鍵約束，不需要指定主鍵名，因為一個表只有一個主鍵，刪除主鍵約束后，非空還存在。

演示代碼

#4. primary key (主鍵約束)
#用來唯一標識表中的一行記錄。
#主鍵約束相當于唯一約束+非空約束的組合，主鍵約束列不允許重復，也不允許出現空值。
#4.1 在CREATE TABLE時添加約束
#一個表中最多只能有一個主鍵約束。#錯誤：Multiple primary key defined
CREATE TABLE test3(
id INT PRIMARY KEY, #列級約束
last_name VARCHAR(15) PRIMARY KEY,
salary DECIMAL(10,2),
email VARCHAR(25)
);# 主鍵約束特征：非空且唯一，用于唯一的標識表中的一條記錄。
CREATE TABLE test4(
id INT PRIMARY KEY, #列級約束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);#MySQL的主鍵名總是PRIMARY，就算自己命名了主鍵約束名也沒用。
CREATE TABLE test5(
id INT , 
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表級約束
CONSTRAINT pk_test5_id PRIMARY KEY(id)  #沒有必要起名字(起不起主鍵的名字都是PRIMARY)
);SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'test5';INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');#錯誤：Duplicate entry '1' for key 'test4.PRIMARY'
INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');#錯誤：Column 'id' cannot be null
INSERT INTO test4(id,last_name,salary,email)
VALUES(NULL,'Tom',4500,'tom@126.com');SELECT * FROM test4;
/*
+----+-----------+---------+-------------+
| id | last_name | salary  | email       |
+----+-----------+---------+-------------+
|  1 | Tom       | 4500.00 | tom@126.com |
+----+-----------+---------+-------------+
*/CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY (NAME,PASSWORD)
);
#如果是多列組合的復合主鍵約束，那么這些列都不允許為空值，并且組合的值不允許重復。
INSERT INTO user1
VALUES(1,'Tom','abc');INSERT INTO user1
VALUES(1,'Tom1','abc');
#錯誤：Column 'name' cannot be null
INSERT INTO user1
VALUES(1,NULL,'abc');SELECT * FROM user1;
/*
+------+------+----------+
| id   | NAME | PASSWORD |
+------+------+----------+
|    1 | Tom  | abc      |
+------+------+----------+
*/#4.2 在ALTER TABLE時添加約束CREATE TABLE test6(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);DESC test6;
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | YES  |     | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/
ALTER TABLE test6
ADD PRIMARY KEY (id);
/*
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int           | NO   | PRI | NULL    |       |
| last_name | varchar(15)   | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
| email     | varchar(25)   | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
*/
#4.3 如何刪除主鍵約束 (在實際開發中，不會去刪除表中的主鍵約束！)
ALTER TABLE test6
DROP PRIMARY KEY;

5. 自增列：AUTO_INCREMENT

5.1 作用

某個字段的值自增

5.2 關鍵字

auto_increment

5.3 特點和要求

（1）一個表最多只能有一個自增長列
（2）當需要產生唯一標識符或順序值時，可設置自增長
（3）自增長列約束的列必須是鍵列（主鍵列，唯一鍵列）
（4）自增約束的列的數據類型必須是整數類型
（5）如果自增列指定了 0 和 null，會在當前最大值的基礎上自增；如果自增列手動指定了具體值，直接賦值為具體值。

錯誤演示：

create table employee(
eid int auto_increment,
ename varchar(20)
);
#	ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
create table employee(
eid int primary key,
ename varchar(20) unique key auto_increment
);
#ERROR 1063 (42000): Incorrect column specifier for column 'ename'  因為ename不是整數類型

5.4 如何指定自增約束

（1）建表時

create table 表名稱(
字段名	數據類型 primary key auto_increment,
字段名	數據類型 unique key not null,
字段名	數據類型 unique key,
字段名	數據類型 not null default 默認值,
);create table 表名稱(
字段名	數據類型 default 默認值 ,
字段名	數據類型 unique key auto_increment,
字段名	數據類型 not null default 默認值,,
primary key(字段名)
);create table employee(
eid int primary key auto_increment,
ename varchar(20)
);
desc employee;			
/*			
+	-------	+-------------	+------	+-----	+---------	+----------------	+
| Field |	Type	| Null	| Key | Default |	Extra	|
+	-------	+-------------	+------	+-----	+---------	+----------------	+
| eid	|	int(11)	| NO	| PRI | NULL	|	auto_increment |
| ename |	varchar(20)	| YES	|	| NULL	|		|
+	-------	+-------------	+------	+-----	+---------	+----------------	+
*/

（2）建表后

alter table 表名稱 modify 字段名 數據類型 auto_increment;

例如：

create table employee(
eid int primary key ,
ename varchar(20)
);alter table employee modify eid int auto_increment;
desc employee;			
/*			
+	-------	+-------------	+------	+-----	+---------	+----------------	+
| Field |	Type	| Null	| Key | Default |	Extra	|
+	-------	+-------------	+------	+-----	+---------	+----------------	+
| eid	|	int(11)	| NO	| PRI | NULL	|	auto_increment |
| ename |	varchar(20)	| YES	|	| NULL	|		|
+	-------	+-------------	+------	+-----	+---------	+----------------	+
*/

5.5 如何刪除自增約束

#給這個字段增加自增約束
#alter table 表名稱 modify 字段名 數據類型 auto_increment;alter table 表名稱 modify 字段名 數據類型; #去掉auto_increment相當于刪除alter table employee modify eid int;
desc employee;	
/*				
+	-------	+-------------	+------	+-----	+---------	+-------	+
| Field |	Type	| Null	| Key | Default	| Extra |
+	-------	+-------------	+------	+-----	+---------	+-------	+
| eid	|	int(11)	| NO	| PRI | NULL	|	|
| ename |	varchar(20)	| YES	|	| NULL	|	|
+	-------	+-------------	+------	+-----	+---------	+-------	+
*/

5.6 MySQL 8.0新特性—自增變量的持久化

在MySQL 8.0之前，自增主鍵AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重啟后，會重置AUTO_INCREMENT=max(primary key)+1，這種現象在某些情況下會導致業務主鍵沖突或者其他難以發現的問題。下面通過案例來對比不同的版本中自增變量是否持久化。在MySQL 5.7版本中，測試步驟如下：創建的數據表中包含自增主鍵的id字段，語句如下：

CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);

插入4個空值，執行如下：

INSERT INTO test1
VALUES(0),(0),(0),(0);

查詢數據表test1中的數據，結果如下：

 SELECT * FROM test1;
/*
+----+| id |+----+|	1 ||	2 ||	3 ||	4 |+----+
*/

刪除id為4的記錄，語句如下：

DELETE FROM test1 WHERE id = 4;

再次插入一個空值，語句如下：

INSERT INTO test1 VALUES(0);

查詢此時數據表test1中的數據，結果如下：

SELECT * FROM test1;
/*
+----+| id |+----+|	1 ||	2 ||	3 ||	5 |+----+*/

從結果可以看出，雖然刪除了id為4的記錄，但是再次插入空值時，并沒有重用被刪除的4，而是分配了5。刪除id為5的記錄，結果如下：

DELETE FROM test1 where id=5;

重啟數據庫，重新插入一個空值。

INSERT INTO test1 values(0);

再次查詢數據表test1中的數據，結果如下：

SELECT * FROM test1;
/*
+----+| id |+----+|	1 ||	2 ||	3 ||	4 |+----+
*/

從結果可以看出，新插入的0值分配的是4，按照重啟前的操作邏輯，此處應該分配6。出現上述結果的主要原因是自增主鍵沒有持久化。在MySQL 5.7系統中，對于自增主鍵的分配規則，是由InnoDB數據字典內部一個計數器來決定的，而該計數器只在內存中維護，并不會持久化到磁盤中。當數據庫重啟時，該計數器會被初始化。

在MySQL 8.0版本中，上述測試步驟最后一步的結果如下：

 SELECT * FROM test1;
/*
+----+| id |+----+|	1 ||	2 ||	3 ||	6 |+----+
*/

從結果可以看出，自增變量已經持久化了。

MySQL 8.0將自增主鍵的計數器持久化到重做日志中。每次計數器發生改變，都會將其寫入重做日志中。如果數據庫重啟，InnoDB會根據重做日志中的信息來初始化計數器的內存值。

演示代碼

#5. 自增長列:AUTO_INCREMENT
# 5.1 在CREATE TABLE時添加
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15) 
);
#開發中，一旦主鍵作用的字段上聲明有AUTO_INCREMENT，則我們在添加數據時，就不要給主鍵
#對應的字段去賦值了。
INSERT INTO test7(last_name)
VALUES('Tom');
INSERT INTO test7(last_name)
VALUES('Tom');SELECT * FROM test7;
/*
+----+-----------+
| id | last_name |
+----+-----------+
|  1 | Tom       |
|  2 | Tom       |
+----+-----------+
*/#當我們向主鍵（含AUTO_INCREMENT）的字段上添加0 或 null時，實際上會自動的往上添加指定的字段的數值
INSERT INTO test7(id,last_name)
VALUES(0,'Tom');INSERT INTO test7(id,last_name)
VALUES(NULL,'Tom');INSERT INTO test7(id,last_name)
VALUES(10,'Tom');INSERT INTO test7(id,last_name)
VALUES(-10,'Tom');
/*
+-----+-----------+
| id  | last_name |
+-----+-----------+
| -10 | Tom       |
|   1 | Tom       |
|   2 | Tom       |
|   3 | Tom       |
|   4 | Tom       |
|  10 | Tom       |
+-----+-----------+
*/
#開發中，一旦主鍵作用的字段上聲明有AUTO_INCREMENT，則我們在添加數據時，就不要給主鍵
#對應的字段去賦值了。#5.2 在ALTER TABLE 時添加
CREATE TABLE test8(
id INT PRIMARY KEY ,
last_name VARCHAR(15) 
);DESC test8;ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;#5.3 在ALTER TABLE 時刪除ALTER TABLE test8
MODIFY id INT ;#5.4 MySQL 8.0新特性—自增變量的持久化
#在MySQL 5.7中演示
CREATE TABLE test9(
id INT PRIMARY KEY AUTO_INCREMENT
);INSERT INTO test9
VALUES(0),(0),(0),(0);SELECT * FROM test9;
/*
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
*/
DELETE FROM test9
WHERE id = 4;INSERT INTO test9
VALUES(0);
/*
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
+----+
*/
DELETE FROM test9
WHERE id = 5;#重啟服務器  net stop mysql57-->net start mysql57SELECT * FROM test9;
/*
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
*/
INSERT INTO test9
VALUES(0);
/*
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
*/
#-->在5.7中，重啟后會接著最大的id值后面增加,即id值是放在內存中維護的
#重啟后依據現有id的最大值繼續增長#在MySQL 8.0中演示
CREATE TABLE test9(
id INT PRIMARY KEY AUTO_INCREMENT
);INSERT INTO test9
VALUES(0),(0),(0),(0);SELECT * FROM test9;DELETE FROM test9
WHERE id = 4;INSERT INTO test9
VALUES(0);DELETE FROM test9
WHERE id = 5;#重啟服務器SELECT * FROM test9;INSERT INTO test9
VALUES(0);
/*
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
+----+
*/
#-->MySQL 8.0將自增主鍵的計數器持久化到 重做日志 中。每次計數器發生改變，都會將其寫入重做日志
#中。如果數據庫重啟，InnoDB會根據重做日志中的信息來初始化計數器的內存值。

6. FOREIGN KEY 約束

6.1 作用

限定某個表的某個字段的引用完整性。

比如：員工表的員工所在部門的選擇，必須在部門表能找到對應的部分。

6.2 關鍵字

FOREIGN KEY

6.3 主表和從表/父表和子表

主表（父表）：被引用的表，被參考的表
從表（子表）：引用別人的表，參考別人的表

例如：員工表的員工所在部門這個字段的值要參考部門表：部門表是主表，員工表是從表。

例如：學生表、課程表、選課表：選課表的學生和課程要分別參考學生表和課程表，學生表和課程表是主表，選課表是從表。

6.4 特點

（1）從表的外鍵列，必須引用/參考主表的主鍵或唯一約束的列為什么？因為被依賴/被參考的值必須是唯一的

（2）在創建外鍵約束時，如果不給外鍵約束命名，默認名不是列名，而是自動產生一個外鍵名（例如student_ibfk_1;），也可以指定外鍵約束名。

（3）創建(CREATE)表時就指定外鍵約束的話，先創建主表，再創建從表

（4）刪表時，先刪從表（或先刪除外鍵約束），再刪除主表

（5）當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數據，需要先刪除從表中依賴該記錄的數據，然后才可以刪除主表的數據

（6）在“從表”中指定外鍵約束，并且一個表可以建立多個外鍵約束

（7）從表的外鍵列與主表被參照的列名字可以不相同，但是數據類型必須一樣，邏輯意義一致。如果類型不一樣，創建子表時，就會出現錯誤“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。

例如：都是表示部門編號，都是int類型。

（8）當創建外鍵約束時，系統默認會在所在的列上建立對應的普通索引。但是索引名是外鍵的約束名。（根據外鍵查詢效率很高）

（9）刪除外鍵約束后，必須手動刪除對應的索引

6.5 添加外鍵約束

（1）建表時

create table 主表名稱(
字段1	數據類型 primary key,
字段2	數據類型
);create table 從表名稱(
字段1	數據類型 primary key,
字段2	數據類型,
[CONSTRAINT <外鍵約束名稱>] FOREIGN KEY（從表的某個字段) references 主表名(被參考字段)
);#(從表的某個字段)的數據類型必須與主表名(被參考字段)的數據類型一致，邏輯意義也一樣#(從表的某個字段)的字段名可以與主表名(被參考字段)的字段名一樣，也可以不一樣--	FOREIGN KEY: 在表級指定子表中的列--	REFERENCES: 標示在父表中的列create table dept( #主表
did int primary key,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(#從表
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
deptid int,	#員工所在的部門
foreign key (deptid) references dept(did)	#在從表中指定外鍵約束
#emp表的deptid和和dept表的did的數據類型一致，意義都是表示部門的編號
);

說明：

（1）主表dept必須先創建成功，然后才能創建emp表，指定外鍵成功。

（2）刪除表時，先刪除從表emp，再刪除主表dept

（3）建表后

一般情況下，表與表的關聯都是提前設計好了的，因此，會在創建表的時候就把外鍵約束定義好。不過，如果需要修改表的設計（比如添加新的字段，增加新的關聯關系），但沒有預先定義外鍵約束，那么，就要用修改表的方式來補充定義。

格式：

ALTER TABLE 從表名 ADD [CONSTRAINT 約束名] FOREIGN KEY (從表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

舉例：

ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

舉例：

create table dept(
did int primary key,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
deptid int	#員工所在的部門
);#這兩個表創建時，沒有指定外鍵的話，那么創建順序是隨意alter table emp add foreign key (deptid) references dept(did);
1234567891011121314

6.6 演示問題

（1）失敗：不是鍵列

create table dept(
did int ,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
deptid int,	#員工所在的部門
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint	原因是dept的did不是鍵列

（2）失敗：數據類型不一致

create table dept(
did int primary key,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
deptid char,	#員工所在的部門
foreign key (deptid) references dept(did)
);#ERROR 1215 (HY000): Cannot add foreign key constraint 
#原因是從表的deptid字段和主表的did字段的數據類型不一致，并且要它倆的邏輯意義一致

（3）成功，兩個表字段名一樣

create table dept(
did int primary key,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
did int,	#員工所在的部門
foreign key (did) references dept(did)
#emp表的deptid和和dept表的did的數據類型一致
#意義都是表示部門的編號
#是否重名沒問題，因為兩個did在不同的表中
);

（4）添加、刪除、修改問題

create table dept(
did int primary key,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
deptid int,	#員工所在的部門
foreign key (deptid) references dept(did)
#emp表的deptid和和dept表的did的數據類型一致，意義都是表示部門的編號
);insert into dept values(1001,'教學部');
insert into dept values(1003, '財務部');#添加從表記錄成功，在添加這條記錄時，要求部門表有1001部門
insert into emp values(1,'張三',1001); insert into emp values(2,'李四',1005);
#添加從表記錄失敗ERROR 1452 (23000): Cannot add（添加） or update（修改） a child row: a foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)) 從表emp添加記錄失敗，因為主表dept沒有1005部門select * from dept;
/*
+------	+--------	+
| did	| dname	|
+------	+--------	+
| 1001	| 教學部	|
| 1003	| 財務部	|
+------	+--------	+*/select *	from emp;/*
+	-----	+-------	+--------	+
| eid	| ename |	deptid |
+	-----	+-------	+--------	+
|	1	| 張三	|	1001 |
+	-----	+-------	+--------	+
*/update emp set deptid = 1002 where eid = 1;#修改從表失敗
ERROR 1452 (23000): Cannot add（添加） or update（修改） a child row（子表的記錄）: a foreign key constraint fails（外鍵約束失敗） (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))#部門表did字段現在沒有1002的值，所以員工表中不能修改員工所在部門deptid為1002update dept set did = 1002 where did = 1001;#修改主表失敗ERROR 1451 (23000): Cannot delete（刪除） or update（修改） a parent row（父表的記錄）: aforeign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY(`deptid`) REFERENCES `dept` (`did`)) 
#部門表did的1001字段已經被emp引用了，所以部門表的1001字段就不能修改了。update dept set did = 1002 where did = 1003;
#修改主表成功	因為部門表的1003部門沒有被emp表引用，所以可以修改delete from dept where did=1001;#刪除主表失敗ERROR 1451 (23000): Cannot delete（刪除） or update（修改） a parent row（父表記錄）: a foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)) 
#因為部門表did的1001字段已經被emp引用了，所以部門表的1001字段對應的記錄就不能被刪除

總結：約束關系是針對雙方的

添加了外鍵約束后，主表的修改和刪除數據受約束
添加了外鍵約束后，從表的添加和修改數據受約束
在從表上建立外鍵，要求主表必須存在
刪除主表時，要求從表從表先刪除，或將從表中外鍵引用該主表的關系先刪除

6.7 約束等級

Cascade方式：在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄
Set null方式：在父表上update/delete記錄時，將子表上匹配記錄的列設為null，但是要注意子表的外鍵列不能為not null
No action方式：如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行update/delete操作
Restrict方式：同no action，都是立即檢查外鍵約束
Set default方式（在可視化工具SQLyog中可能顯示空白）：父表有變更時，子表將外鍵列設置成一個默認的值，但Innodb不能識別

如果沒有指定等級，就相當于Restrict方式。

對于外鍵約束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

（1）演示1：on update cascade on delete set null

create table dept(
did int primary key,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
deptid int,	#員工所在的部門
foreign key (deptid) references dept(did)	on update cascade on delete set null
#把修改操作設置為級聯修改等級，把刪除操作設置為set null等級
);insert into dept values(1001,'教學部');
insert into dept values(1002, '財務部');
insert into dept values(1003, '咨詢部');
insert into emp values(1,'張三',1001); #在添加這條記錄時，要求部門表有1001部門 insert into emp values(2,'李四',1001); insert into emp values(3,'王五',1002);select * from dept;select * from emp;#修改主表成功，從表也跟著修改，修改了主表被引用的字段1002為1004，從表的引用字段就跟著修改為1004了update dept set did = 1004 where did = 1002;
select * from dept;
/*
+------	+--------	+
| did	| dname	|
+------	+--------	+
| 1001	| 教學部 |	
| 1003	| 咨詢部 |	
|	1004 | 財務部 | #原來是1002，修改為1004
+------+--------+*/select *	from emp;
/*
+	-----	+-------	+--------	+
| eid |	ename |	deptid |
+	-----	+-------	+--------	+
|	1	|	張三	|	1001	|
|	2	|	李四	|	1001	|
|	3 | 王五 |   1004 | #原來是1002，跟著修改為1004
+-----+-------+--------+*/#刪除主表的記錄成功，從表對應的字段的值被修改為null
delete from dept where did = 1001;
select * from dept;
/*
+------	+--------	+
| did	| dname	| #記錄1001部門被刪除了
+------	+--------	+
| 1003	| 咨詢部	|
| 1004	| 財務部	|
+------	+--------	+*/select	* from emp;
/*
+	-----+-------	+--------	+
| eid | ename	| deptid |
+	-----+-------	+--------	+
|	1 | 張三 |   NULL | #原來引用1001部門的員工，deptid字段變為null
|	2 | 李四 |   NULL |
|	3 | 王五 | 1004 | */
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263

（2）演示2：on update set null on delete

cascade
create table dept(
did int primary key,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
deptid int,	#員工所在的部門
foreign key (deptid) references dept(did)	on update set null on delete cascade
#把修改操作設置為set null等級，把刪除操作設置為級聯刪除等級
);insert into dept values(1001,'教學部');
insert into dept values(1002, '財務部');
insert into dept values(1003, '咨詢部');
insert into emp values(1,'張三',1001); #在添加這條記錄時，要求部門表有1001部門 insert into emp values(2,'李四',1001); insert into emp values(3,'王五',1002);select * from dept;
/*
+	------	+--------	+
| did	| dname	|
+	------	+--------	+
| 1001	| 教學部 |	
| 1002	| 財務部 |	
| 1003	| 咨詢部 |	
+	------	+--------	+
*/select *	from emp;/*
+	-----	+-------	+--------	+
| eid |	ename |	deptid |
+	-----	+-------	+--------	+
|	1	|	張三	|	1001	|
|	2	|	李四	|	1001	|
|	3	|	王五	|	1002	|
+	-----	+-------	+--------	+
*/
#修改主表，從表對應的字段設置為nullupdate dept set did = 1004 where did = 1002; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0select *	from dept;
/*
+	------	+--------	+
|	did	|	dname	|
+	------	+--------	+
| 1001	| 教學部 |
|	1003	|	咨詢部 |
|	1004 | 財務部 | #原來did是1002
+------+--------+*/select *	from emp;
/*
+	-----	+-------	+--------	+
| eid |	ename |	deptid |
+	-----	+-------	+--------	+
|	1	|	張三	|	1001	|
|	2	|	李四	|	1001	|
|	3 | 王五 | NULL | #原來deptid是1002，因為部門表1002被修改了，1002沒有對應的了，就設置為 null
+-----+-------+--------+*/#刪除主表的記錄成功，主表的1001行被刪除了，從表相應的記錄也被刪除了 delete from dept where did=1001;
Query OK, 1 row affected (0.00 sec)
select * from dept;
/*
+	------	+--------	+
| did		|	dname	| #部門表中1001部門被刪除
+	------	+--------	+
| 1003		|	咨詢部 |	
| 1004		|	財務部 |	
+	------	+--------	+*/select * from emp;
/*
+	-----	+	-------	+--------	+
| eid	| ename | deptid |#原來1001部門的員工也被刪除了
+	-----	+	-------	+--------	+
|	3	| 王五 |	NULL |
+	-----	+	-------	+--------	+
*/
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384

（3）演示：on update cascade on delete cascade

create table dept(
did int primary key,	#部門編號
dname varchar(50)	#部門名稱
);create table emp(
eid int primary key,	#員工編號
ename varchar(5),	#員工姓名
deptid int,	#員工所在的部門
foreign key (deptid) references dept(did)	on update cascade on delete cascade
#把修改操作設置為級聯修改等級，把刪除操作也設置為級聯刪除等級
);insert into dept values(1001,'教學部');
insert into dept values(1002, '財務部');
insert into dept values(1003, '咨詢部');
insert into emp values(1,'張三',1001); 
#在添加這條記錄時，要求部門表有1001部門 insert into emp values(2,'李四',1001); insert into emp values(3,'王五',1002);select * from dept;
/*
+	------	+--------	+
| did	| dname	|
+	------	+--------	+
| 1001	| 教學部 |	
| 1002	| 財務部 |	
| 1003	| 咨詢部 |	
+	------	+--------	+
*/select *	from emp;
/*
+	-----	+-------	+--------	+
| eid |	ename |	deptid |
+	-----	+-------	+--------	+
|	1	|	張三	|	1001	|
|	2	|	李四	|	1001	|
|	3	|	王五	|	1002	|
+	-----	+-------	+--------	+
*/#修改主表，從表對應的字段自動修改
update dept set did = 1004 where did = 1002; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0select * from dept;
/*
+------	+--------	+
| did	| dname	|
+------	+--------	+
| 1001	| 教學部 |	
| 1003	| 咨詢部 |	
|	1004 | 財務部 | #部門1002修改為1004
+------+--------+*/select *	from emp;
/*
+	-----	+-------	+--------	+
| eid |	ename |	deptid |
+	-----	+-------	+--------	+
|	1	|	張三	|	1001	|
|	2	|	李四	|	1001	|
|	3 | 王五 |   1004 | #級聯修改
+-----+-------+--------+*/#刪除主表的記錄成功，主表的1001行被刪除了，從表相應的記錄也被刪除了 mysql> delete from dept where did=1001;
Query OK, 1 row affected (0.00 sec)select * from dept;
/*
+------	+--------	+
| did	| dname	| #1001部門被刪除了
+------	+--------	+
| 1003	| 咨詢部 |	
| 1004	| 財務部 |	
+------	+--------	+*/select * from emp;
/*
+	-----	+-------	+--------	+
| eid	| ename | deptid |  #1001部門的員工也被刪除了
+	-----	+-------	+--------	+
|	3	| 王五	|	1004 |
+	-----	+-------	+--------	+*/
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384

6.8 刪除外鍵約束

流程如下：

(1)第一步先查看約束名和刪除外鍵約束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';#查看某個表的約束名ALTER TABLE 從表名 DROP FOREIGN KEY 外鍵約束名;（2）第二步查看索引名和刪除索引。（注意，只能手動刪除）SHOW INDEX FROM 表名稱; #查看某個表的索引名ALTER TABLE 從表名 DROP INDEX 索引名;
12345678910

舉例：

SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';alter table emp drop foreign key emp_ibfk_1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0show index from emp;alter table emp drop index deptid;
Query OK, 0 rows affected (0.01 sec)
Records: 0	Duplicates: 0	Warnings: 0show index from emp;

6.9 開發場景

問題1：如果兩個表之間有關系（一對一、一對多），比如：員工表和部門表（一對多），它們之間是否一定要建外鍵約束？
答：不是的

問題2：建和不建外鍵約束有什么區別？
答：建外鍵約束，你的操作（創建表、刪除表、添加、修改、刪除）會受到限制，從語法層面受到限制。例如：在員工表中不可能添加一個員工信息，它的部門的值在部門表中找不到。

不建外鍵約束，你的操作（創建表、刪除表、添加、修改、刪除）不受限制，要保證數據的引用完整性，只能依靠程序員的自覺，或者是在Java程序中進行限定。例如：在員工表中，可以添加一個員工的信息，它的部門指定為一個完全不存在的部門。

問題3：那么建和不建外鍵約束和查詢有沒有關系？
答：沒有
在 MySQL 里，外鍵約束是有成本的，需要消耗系統資源。對于大并發的 SQL 操作，有可能會不適合。比如大型網站的中央數據庫，可能會因為外鍵約束的系統開銷而變得非常慢。所以， MySQL 允許你不使用系統自帶的外鍵約束，在應用層面完成檢查數據一致性的邏輯。也就是說，即使你不用外鍵約束，也要想辦法通過應用層面的附加邏輯，來實現外鍵約束的功能，確保數據的一致性。

6.10 阿里開發規范

【強制】不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。

說明：（概念解釋）學生表中的 student_id 是主鍵，那么成績表中的 student_id 則為外鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外鍵與級聯更新適用于單機低并發，不適合分布式、高并發集群；級聯更新是強阻塞，存在數據庫更新風暴的風險；外鍵影響數據庫的插入速度。

演示代碼

#6.foreign key (外鍵約束)
#限定某個表的某個字段的引用完整性。
#6.1 在CREATE TABLE 時添加#主表和從表；父表和子表#①先創建主表
CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);
#②再創建從表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,#表級約束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id));#上述操作報錯，因為主表中的dept_id上沒有主鍵約束或唯一性約束。
#③ 添加
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);DESC dept1;
/*
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int         | NO   | PRI | NULL    |       |
| dept_name | varchar(15) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
*/#④ 再創建從表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,#表級約束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id));DESC emp1;
/*
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| emp_id        | int         | NO   | PRI | NULL    | auto_increment |
| emp_name      | varchar(15) | YES  |     | NULL    |                |
| department_id | int         | YES  | MUL | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
*/
SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'emp1';
/*
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | dbtest13          | PRIMARY         | dbtest13     | emp1       | PRIMARY KEY     | YES      |
| def                | dbtest13          | fk_emp1_dept_id | dbtest13     | emp1       | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
*/#6.2 演示外鍵的效果
#添加失敗
INSERT INTO emp1
VALUES(1001,'Tom',10);#
INSERT INTO dept1
VALUES(10,'IT');
#在主表dept1中添加了10號部門以后，我們就可以在從表中添加10號部門的員工
INSERT INTO emp1
VALUES(1001,'Tom',10);#刪除失敗
DELETE FROM dept1
WHERE dept_id = 10;#更新失敗
UPDATE dept1
SET dept_id = 20
WHERE dept_id = 10;#6.3 在ALTER TABLE時添加外鍵約束
CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'emp2';#6.4 ###  約束等級-- `Cascade方式`：在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄 -- `Set null方式`：在父表上update/delete記錄時，將子表上匹配記錄的列設為null，但是要注意子表的外鍵列不能為not null  -- `No action方式`：如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行update/delete操作  -- `Restrict方式`：同no action， 都是立即檢查外鍵約束-- `Set default方式`（在可視化工具SQLyog中可能顯示空白）：父表有變更時，子表將外鍵列設置成一個默認的值，但Innodb不能識別#演示：
# on update cascade on delete set null
CREATE TABLE dept(did INT PRIMARY KEY,		#部門編號dname VARCHAR(50)			#部門名稱
);CREATE TABLE emp(eid INT PRIMARY KEY,  #員工編號ename VARCHAR(5),     #員工姓名deptid INT,		  #員工所在的部門FOREIGN KEY (deptid) REFERENCES dept(did)  ON UPDATE CASCADE ON DELETE SET NULL#把修改操作設置為級聯修改等級，把刪除操作設置為set null等級
);INSERT INTO dept VALUES(1001,'教學部');
INSERT INTO dept VALUES(1002, '財務部');
INSERT INTO dept VALUES(1003, '咨詢部');INSERT INTO emp VALUES(1,'張三',1001); #在添加這條記錄時，要求部門表有1001部門
INSERT INTO emp VALUES(2,'李四',1001);
INSERT INTO emp VALUES(3,'王五',1002);UPDATE dept
SET did = 1004
WHERE did = 1002;DELETE FROM dept
WHERE did = 1004;SELECT * FROM dept;
/*
+------+-----------+
| did  | dname     |
+------+-----------+
| 1001 | 教學部    |
| 1003 | 咨詢部    |
+------+-----------+
*/
SELECT * FROM emp;
/*
+-----+--------+--------+
| eid | ename  | deptid |
+-----+--------+--------+
|   1 | 張三   |   1001 |
|   2 | 李四   |   1001 |
|   3 | 王五   |   NULL |
+-----+--------+--------+
*/
#結論：對于外鍵約束，最好是采用: `ON UPDATE CASCADE ON DELETE RESTRICT` 的方式。#6.5 刪除外鍵約束
#一個表中可以聲明有多個外鍵約束
USE atguigudb;
SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'employees';USE dbtest13;SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'emp1';#刪除外鍵約束ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;#再手動的刪除外鍵約束對應的普通索引
SHOW INDEX FROM emp1;ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;

7. CHECK 約束

7.1 作用

檢查某個字段的值是否符號xx要求，一般指的是值的范圍

7.2 關鍵字

CHECK

7.3 說明：MySQL 5.7 不支持

MySQL5.7 可以使用check約束，但check約束對數據驗證沒有任何作用。添加數據時，沒有任何錯誤或警告

但是MySQL 8.0中可以使用check約束了。

create table employee(
eid int primary key,
ename varchar(5),
gender char check ('男' or '女')
);insert into employee values(1,'張三','妖');
select *	from employee;
/*+	-----	+-------	+--------	+
| eid	| ename |	gender |
+	-----	+-------	+--------	+
|	1	| 張三	|	妖	|
+	-----	+-------	+--------	+*/

再舉例

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

再舉例

age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

再舉例

CHECK(height>=0 AND height<3)

演示代碼

#7. check 約束
#檢查某個字段的值是否符號xx要求，一般指的是值的范圍
# MySQL5.7 不支持CHECK約束，MySQL8.0支持CHECK約束。
CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary > 2000)
);INSERT INTO test10
VALUES(1,'Tom',2500);#添加失敗
INSERT INTO test10
VALUES(2,'Tom1',1500);SELECT * FROM test10;

8. DEFAULT約束

8.1 作用

給某個字段/某列指定默認值，一旦設置默認值，在插入數據時，如果此字段沒有顯式賦值，則賦值為默認值。

8.2 關鍵字

DEFAULT

8.3 如何給字段加默認值

（1）建表時

create table 表名稱(
字段名	數據類型 primary key,
字段名	數據類型 unique key not null,
字段名	數據類型 unique key,
字段名	數據類型 not null default 默認值,
);
create table 表名稱(
字段名	數據類型 default 默認值 ,
字段名	數據類型 not null default 默認值,
字段名	數據類型 not null default 默認值,
primary key(字段名),
unique key(字段名)
);

說明：默認值約束一般不在唯一鍵和主鍵列上加

create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默認是空字符串
);
desc employee;						
/*+--------	+-------------	+------	+-----	+---------	+-------	+
| Field	|	Type	| Null	| Key	| Default	| Extra |
+--------	+-------------	+------	+-----	+---------	+-------	+
| eid	|	int(11)	|	NO	| PRI	| NULL	|	|
| ename	|	varchar(20) |	NO	|	| NULL	|	|
| gender	|	char(1)	|	YES	|	| 男	|	|
| tel	|	char(11)	| NO	|	|	|	|
+--------	+-------------	+------	+-----	+---------	+-------	+*/insert into employee values(1,'汪飛','男','13700102535'); #成功select *	from employee;	
/*+	-----	+-------	+--------	+-------------	+
| eid	| ename |	gender | tel	|
+	-----	+-------	+--------	+-------------	+
|	1	| 汪飛	|	男	| 13700102535 |	
+	-----	+-------	+--------	+-------------	+*/insert into employee(eid,ename) values(2,'天琪'); #成功select *	from employee;	
/*+	-----	+-------	+--------	+-------------	+
| eid |	ename |	gender | tel	|
+	-----	+-------	+--------	+-------------	+
|	1	|	汪飛	|	男	| 13700102535 |
|	2	|	天琪	|	男	|	|
+	-----	+-------	+--------	+-------------+*/insert into employee(eid,ename) values(3,'二虎'); 
#ERROR 1062 (23000): Duplicate entry '' for key 'tel'#如果tel有唯一性約束的話會報錯，如果tel沒有唯一性約束，可以添加成功

再舉例：

CREATE TABLE myemp(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(15),
salary DOUBLE(10,2) DEFAULT 2000
);

（2）建表后

alter table 表名稱 modify 字段名 數據類型 default 默認值;
#如果這個字段原來有非空約束，你還保留非空約束
#那么在加默認值約束時，還得保留非空約束，否則非空約束就被刪除了#同理，在給某個字段加非空約束也一樣，如果這個字段原來有默認值約束，你想保留，也要在modify語句中保留默認值約束，否則就刪除了alter table 表名稱 modify 字段名 數據類型 default 默認值 not null;create table employee(
eid int primary key,
ename varchar(20),
gender char,
tel char(11) not null
);
desc employee;						
/*+--------	+-------------	+------	+-----	+---------	+-------	+
| Field	| Type	| Null | Key |	Default	| Extra |
+--------	+-------------	+------	+-----	+---------	+-------	+
| eid	| int(11)	| NO	| PRI |	NULL	|	|
| ename	| varchar(20) | YES	|	|	NULL	|	|
| gender	| char(1)	| YES	|	|	NULL	|	|
| tel	| char(11)	| NO	|	|	NULL	|	|
+--------	+-------------	+------	+-----	+---------	+-------	+*/alter table employee modify gender char default '男';#給gender字段增加默認值約束 alter table employee modify tel char(11) default '';#給tel字段增加默認值約束desc employee;						
/*+--------	+-------------	+------	+-----	+---------	+-------	+
| Field	| Type	| Null | Key |	Default	| Extra |
+--------	+-------------	+------	+-----	+---------	+-------	+
| eid	| int(11)	| NO	| PRI |	NULL	|	|
| ename	| varchar(20) | YES	|	|	NULL	|	|
| gender	| char(1)	| YES	|	|	男	|	|
| tel	| char(11)	| YES	|	|		|	|
+--------	+-------------	+------	+-----	+---------	+-------	+*/alter table employee modify tel char(11) default ''	not null;
#給tel字段增加默認值約束，并保留非空約束desc employee;						
/*+--------	+-------------	+------	+-----	+---------	+-------	+
| Field	|	Type	| Null	| Key	| Default	| Extra |
+--------	+-------------	+------	+-----	+---------	+-------	+
| eid	|	int(11)	|	NO	| PRI	| NULL	|	|
| ename	|	varchar(20) |	YES	|	| NULL	|	|
| gender	|	char(1)	|	YES	|	| 男	|	|
| tel	|	char(11)	| NO	|	|	|	|
+--------	+-------------	+------	+-----	+---------	+-------	+*/

8.4 如何刪除默認值約束

alter table 表名稱 modify 字段名 數據類型 ;
#刪除默認值約束，也不保留非空約束alter table 表名稱 modify 字段名 數據類型	not null; 
#刪除默認值約束，保留非空約束alter table employee modify gender char;#刪除gender字段默認值約束，如果有非空約束，也一并刪除 alter table employee modify tel char(11) not null;#刪除tel字段默認值約束，保留非空約束desc employee;						
/*+--------	+-------------	+------	+-----	+---------	+-------	+
| Field	| Type	| Null | Key |	Default	| Extra |
+--------	+-------------	+------	+-----	+---------	+-------	+
| eid	| int(11)	| NO	| PRI |	NULL	|	|
| ename	| varchar(20) | YES	|	|	NULL	|	|
| gender	| char(1)	| YES	|	|	NULL	|	|
| tel	| char(11)	| NO	|	|	NULL	|	|
+--------	+-------------	+------	+-----	+---------	+-------	+*/

演示代碼

#8.DEFAULT約束
#給某個字段/某列指定默認值，一旦設置默認值，在插入數據時，
#如果此字段沒有顯式賦值，則賦值為默認值。
#8.1 在CREATE TABLE添加約束
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);DESC test11;INSERT INTO test11(id,last_name,salary)
VALUES(1,'Tom',3000);INSERT INTO test11(id,last_name)
VALUES(2,'Tom1');SELECT * 
FROM test11;#8.2 在ALTER TABLE添加約束
CREATE TABLE test12(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2)
);DESC test12;ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 2500;#8.3 在ALTER TABLE刪除約束
ALTER TABLE test12
MODIFY salary DECIMAL(8,2);SHOW CREATE TABLE test12;

9. 面試

面試1、為什么建表時，加 not null default ‘’ 或 default 0
答：
不想讓表中出現null值。

面試2、為什么不想要 null 的值
答:
（1）不好比較。null是一種特殊值，比較時只能用專門的is null 和 is not null來比較。碰到運算符，通常返回null。
（2）效率不高。影響提高索引效果。因此，往往在建表時 not null default ‘’ 或 default 0

面試3、帶AUTO_INCREMENT約束的字段值是從1開始的嗎？
在MySQL中，默認AUTO_INCREMENT的初始值是1，每新增一條記錄，字段值自動加1。設置自增屬性（AUTO_INCREMENT）的時候，還可以指定第一條插入記錄的自增字段的值，這樣新插入的記錄的自增字段值從初始值開始遞增，如在表中插入第一條記錄，同時指定id值為5，則以后插入的記錄的id值就會從6開始往上增加。添加主鍵約束時，往往需要設置字段自動增加屬性。

面試4、并不是每個表都可以任意選擇存儲引擎？
外鍵約束（FOREIGN KEY）不能跨引擎使用。
MySQL支持多種存儲引擎，每一個表都可以指定一個不同的存儲引擎，需要注意的是：外鍵約束是用來保證數據的參照完整性的，如果表之間需要關聯外鍵，卻指定了不同的存儲引擎，那么這些表之間是不能創建外鍵約束的。所以說，存儲引擎的選擇也不完全是隨意的

課后練習

#第13章_約束的課后練習
#已經存在數據庫test04_emp，兩張表emp2和dept2
#練習1：
CREATE DATABASE test04_emp;USE test04_emp;CREATE TABLE emp2(
id INT,
emp_name VARCHAR(15)
);CREATE TABLE dept2(
id INT,
dept_name VARCHAR(15)
);#1.向表emp2的id列中添加PRIMARY KEY約束ALTER TABLE emp2
ADD CONSTRAINT pk_emp2_id PRIMARY KEY(id);#2.向表dept2的id列中添加PRIMARY KEY約束ALTER TABLE dept2
ADD PRIMARY KEY(id);#3.向表emp2中添加列dept_id，并在其中定義FOREIGN KEY約束，與之相關聯的列是dept2表中的id列。ALTER TABLE emp2
ADD dept_id INT;DESC emp2;
/*
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| emp_name | varchar(15) | YES  |     | NULL    |       |
| dept_id  | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
*/ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_deptid FOREIGN KEY(dept_id) REFERENCES dept2(id);#練習2：
#承接《第11章_數據處理之增刪改》的綜合案例。
USE test01_library;DESC books;
/*
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | YES  |     | NULL    |       |
| name    | varchar(50)  | YES  |     | NULL    |       |
| authors | varchar(100) | YES  |     | NULL    |       |
| price   | float        | YES  |     | NULL    |       |
| pubdate | year         | YES  |     | NULL    |       |
| note    | varchar(100) | YES  |     | NULL    |       |
| num     | int          | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
*/
#根據題目要求給books表中的字段添加約束
#方式1：
ALTER TABLE books
ADD PRIMARY KEY (id);ALTER TABLE books
MODIFY id INT AUTO_INCREMENT;
#方式2：
ALTER TABLE books
MODIFY id INT PRIMARY KEY AUTO_INCREMENT;#針對于非id字段的操作
ALTER TABLE books
MODIFY NAME VARCHAR(50) NOT NULL;ALTER TABLE books
MODIFY AUTHORS VARCHAR(100) NOT NULL;ALTER TABLE books
MODIFY price FLOAT NOT NULL;ALTER TABLE books
MODIFY pubdate YEAR NOT NULL;ALTER TABLE books
MODIFY num INT NOT NULL;#練習3：
#1. 創建數據庫test04_company
CREATE DATABASE IF NOT EXISTS test04_company CHARACTER SET 'utf8';USE test04_company;#2. 按照下表給出的表結構在test04_company數據庫中創建兩個數據表offices和employeesCREATE TABLE IF NOT EXISTS offices(
officeCode INT(10) PRIMARY KEY ,
city VARCHAR(50) NOT NULL,
address VARCHAR(50) ,
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15),
CONSTRAINT uk_off_poscode UNIQUE(postalCode));DESC offices;CREATE TABLE employees(
employeeNumber INT PRIMARY KEY AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25) UNIQUE,
officeCode INT(10) NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME NOT NULL,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT fk_emp_offcode FOREIGN KEY (officeCode) REFERENCES offices(officeCode));DESC employees;#3. 將表employees的mobile字段修改到officeCode字段后面
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER officeCode;#4. 將表employees的birth字段改名為employee_birth
ALTER TABLE employees
CHANGE birth employee_birth DATETIME;#5. 修改sex字段，數據類型為CHAR(1)，非空約束
ALTER TABLE employees
MODIFY sex CHAR(1) NOT NULL;#6. 刪除字段note
ALTER TABLE employees
DROP COLUMN note;#7. 增加字段名favoriate_activity，數據類型為VARCHAR(100)
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);#8. 將表employees名稱修改為employees_info
RENAME TABLE employees
TO employees_info;#錯誤：Table 'test04_company.employees' doesn't exist
DESC employees;DESC employees_info;