1. 約束

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

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

2. 根據(jù)約束起的作用，可分為：

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

• 列級約束和表級約束區(qū)別：表級約束多了 CONSTRAINT constraint_name

3. 基本語句：

• 在創(chuàng)建表時規(guī)定約束：CREATE TABLE…
• 在表創(chuàng)建后添加約束：ALTER TABLE …
• 查看某個表已有的約束：
  SELECT * FROM information_schema.table_constraints WHERE table_name = ‘table name’;
• 查看表索引：SHOW INDEX FROM tablename;

2. 非空約束 NOT NULL

• 限定某個字段/某列的值不允許為空：NOT NULL。
• 所有的類型的值都可以是NULL
• 非空約束只能出現(xiàn)在表對象的列上，只能某個列單獨限定非空，不能組合非空
• 一個表可以有很多列都分別限定了非空
• 空字符串’'不等于NULL，0也不等于NULL
• 修改ALTER TABLE 字段約束時，如果要保留字段原有的非空約束，在加其他約束時，需要保留NOT NULL，否則非空約束就會被刪除。

# 建表時非空約束
CREATE TABLE emp (id INT(10) NOT NULL,name VARCHAR(20) NOT NULL,sex CHAR NULL
);

# 建表后非空約束
ALTER TABLE emp 
MODIFY sex VARCHAR(20) NOT NULL;ALTER TABLE emp 
MODIFY name VARCHAR(15) DEFAULT 'abc' NOT NULL;# 刪除非空約束
ALTER TABLE emp 
MODIFY sex VARCHAR(20) NULL;

3. 唯一性約束 UNIQUE

• 用來限制某個字段/某列的值不能重復：UNIQUE。
• UNIQUE KEY 等同于 UNIQUE.
• 單列用法UNIQUE()，組合用法UNIQUE(item, item2, …)。
• 同一個表可以有多個唯一約束。
• 唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一。
• 唯一性約束允許列值為空。
• 如果創(chuàng)建唯一約束時未指定名稱，如果是單列，就默認和列名相同；如果是組合列，那么默認和()中排在第一個的列名相同。也可以自定義唯一性約束名。
• MySQL會給唯一約束的列上默認自動創(chuàng)建一個唯一索引。
• 刪除唯一約束只能通過刪除唯一索引的方式刪除。
• 刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。

# 建表時唯一性約束
CREATE TABLE stu3 (sid INT NOT NULL UNIQUE KEY,cardid VARCHAR(20) UNIQUE,phone INT UNIQUE KEY,name VARCHAR(25),password VARCHAR(16),address VARCHAR(18),age INT,childs INT,UNIQUE KEY(name, password),UNIQUE(address, age),CONSTRAINT stu3_constraint UNIQUE(sid, cardid)
);
-- UNIQUE KEY 等同于 UNIQUE
-- NOT NULL && UNIQUE 等同于 PRIMARY KEY
-- CONSTRAINT ... UNIQUE() 表級約束CREATE TABLE stu2 (sid INT NOT NULL,name VARCHAR(25) DEFAULT 'abc',password VARCHAR(16),cardid VARCHAR(20) UNIQUE,phone INT UNIQUE KEY,address VARCHAR(18),age INT,childs INT,CONSTRAINT stu2_name_pwd UNIQUE(name, password) 
);DESC stu2;
DROP TABLE stu2;

# 建表后唯一性約束
ALTER TABLE 表名稱 ADD UNIQUE KEY (字段列表);# sid 既非NULL又唯一時會變?yōu)橹麈IPrimaryKey
ALTER TABLE stu2 ADD UNIQUE KEY(sid); 
-- sid key ： PRIALTER TABLE stu2 ADD UNIQUE KEY(address, age, childs);
ALTER TABLE stu2 ADD CONSTRAINT stu2_age_childs UNIQUE(address, age, childs);# 也可以直接改變字段屬性
ALTER TABLE stu2 MODIFY sid INT NOT NULL UNIQUE;
-- sid key: PRI 
ALTER TABLE stu2 MODIFY sid INT UNIQUE;
-- sid key: UNISELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'stu2';# 刪除唯一性約束
ALTER TABLE stu2 DROP INDEX stu2_age_childs;

4. 主鍵約束 PRIMARY KEY

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

# 列級約束PRIMARY KEY
CREATE TABLE temp1(id INT PRIMARY KEY,name VARCHAR(20)
);
# 列級約束組合
CREATE TABLE temp2 (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(20),PRIMARY KEY(id, name)
);
# 表級約束
CREATE TABLE temp2 (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(20),CONSTRAINT temp2_id_pk PRIMARY KEY(id)
);
# 表級約束組合
CREATE TABLE temp3 (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(20),CONSTRAINT temp3_id_pk PRIMARY KEY(id, name)
);
-- id 和 name 均為非NULL且PRI# 建表后 PRIMARY KEY
ALTER TABLE temp ADD PRIMARY KEY(id);
ALTER TABLE temp ADD PRIMARY KEY(id, name);# 刪除主鍵約束，不需要指定主鍵名
ALTER TABLE temp DROP PRIMARY KEY;

5. 自增約束 AUTO_INCREMENT

• 某個字段的值自增：AUTO_INCREMENT。
• 一個表最多只能有一個自增長列
• 當需要產(chǎn)生唯一標識符或順序值時，可設置自增長
• 自增長列約束的列必須是鍵列（主鍵列PRIMARY KEY 或 唯一鍵列UNIQUE KEY）
• 自增約束的列的數(shù)據(jù)類型必須是整數(shù)類型
• 如果自增列指定了 0 和 null，會在當前最大值的基礎上自增；如果自增列手動指定了具體值，直接賦值為具體值。
• 自增顯示在Extra項

create table 表名稱(字段名 數(shù)據(jù)類型 primary key auto_increment,字段名 數(shù)據(jù)類型 unique key not null,字段名 數(shù)據(jù)類型 unique key,字段名 數(shù)據(jù)類型 not null default 默認值,
);
create table 表名稱(字段名 數(shù)據(jù)類型 default 默認值,字段名 數(shù)據(jù)類型 unique key auto_increment,字段名 數(shù)據(jù)類型 not null default 默認值,,primary key(字段名)
);-- 不建議只寫KEY，雖然默認key = primary key
CREATE TABLE auto(id INT KEY AUTO_INCREMENT,name VARCHAR(20)
);CREATE TABLE auto2(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20)
);CREATE TABLE auto3(id INT UNIQUE KEY AUTO_INCREMENT,name VARCHAR(20) PRIMARY KEY
);# 建表后增加自增
ALTER TABLE auto3 
MODIFY id INT AUTO_INCREMENT;# 去掉auto_increment相當于刪除
ALTER TABLE auto3 
MODIFY id INT;

6. 外鍵約束FOREIGN KEY

• 限定某個表的某個字段的引用完整性：FOREIGN KEY。 比如：員工表的員工所在部門的選擇，必須在部門表能找到對應的部分。
• 主表（父表）：被引用的表，被參考的表。從表（子表）：引用別人的表，參考別人的表。例如：學生表、課程表、選課表：選課表的學生和課程要分別參考學生表和課程表，學生表和課程表是主表，選課表是從表。
• 從表的外鍵列，必須參考主表的主鍵或唯一約束的列。因為被依賴/被參考的值必須是唯一的。
• 在創(chuàng)建外鍵約束時，可以指定外鍵約束名。如果不給外鍵約束命名，默認名不是列名，而是自動產(chǎn)生一個外鍵名（例如student_ibfk_1）。
• 創(chuàng)建表時就指定外鍵約束的話，先創(chuàng)建主表，再創(chuàng)建從表
• 刪表時，先刪從表（或先刪除外鍵約束），再刪除主表
• 當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數(shù)據(jù)，需要先刪除從表中依賴該記錄的數(shù)據(jù)，然后才可以刪除主表的數(shù)據(jù)
• 在從表中指定外鍵約束，并且一個表可以建立多個外鍵約束
• 從表的外鍵列與主表被參照的列名字可以不相同，但是數(shù)據(jù)類型必須一樣，邏輯意義一致。如果類型不一樣，創(chuàng)建子表時，就會出現(xiàn)錯誤“ERROR 1005 (HY000): Can’t createtable’database.tablename’(errno: 150)”。
• 當創(chuàng)建外鍵約束時，系統(tǒng)默認會在所在的列上建立對應的普通索引。但是索引名是外鍵的約束名。（根據(jù)外鍵查詢效率很高）
• 刪除外鍵約束后，必須手動刪除對應的索引。
• 兩個表之間有關系，但不一定必須要外鍵約束。
• 外鍵約束需要消耗系統(tǒng)資源，對于大并發(fā)的 SQL 操作，可能會因為外鍵約束的系統(tǒng)開銷而變得非常慢。建議通過應用層面完成檢查數(shù)據(jù)一致性的邏輯。
• 阿里開發(fā)規(guī)范：不得使用外鍵與級聯(lián)，一切外鍵概念必須在應用層解決。外鍵與級聯(lián)更新適用于單機低并發(fā)，不適合分布式、高并發(fā)集群；級聯(lián)更新是強阻塞，存在數(shù)據(jù)庫更新風暴的風險；外鍵影響數(shù)據(jù)庫的插入速度。

create table 主表名稱(字段1 數(shù)據(jù)類型 primary key,字段2 數(shù)據(jù)類型
);
create table 從表名稱(字段1 數(shù)據(jù)類型 primary key,字段2 數(shù)據(jù)類型,[CONSTRAINT <外鍵約束名稱>] FOREIGN KEY（從表的某個字段) references 主表名(被參考字段)
);CREATE TABLE depx(did INT PRIMARY KEY,dname VARCHAR(20)
);
CREATE TABLE empx(eid INT PRIMARY KEY,ename VARCHAR(20),depxid INT,FOREIGN KEY (depxid) REFERENCES depx(did)
);
-- depxid KEY: MUL DESC depx;
DESC empx;

• 建表后添加外鍵約束

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

• 刪除外鍵約束

# (1)先查看從表約束名，刪除外鍵約束
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'empx';
# 刪除外鍵
ALTER TABLE empx 
DROP FOREIGN KEY empx_ibfk_1;#（2）查看索引名和刪除索引
SHOW INDEX FROM empx;
# 刪除索引
ALTER TABLE empx 
DROP INDEX depxid;

7. 默認值約束 DEFAULT

• 給某個字段/某列指定默認值，一旦設置默認值，在插入數(shù)據(jù)時，如果此字段沒有顯式賦值，則賦值為默認值：DEFAULT。
• 默認值約束一般不在唯一鍵和主鍵列上加。

create table 表名稱(字段名 數(shù)據(jù)類型 default 默認值 ,字段名 數(shù)據(jù)類型 not null default 默認值,字段名 數(shù)據(jù)類型 not null default 默認值,primary key(字段名),unique key(字段名)
);create table employee(eid INT primary key,salary DOUBLE(10,2) DEFAULT 2000ename varchar(20) not null,gender char default '男',tel char(11) not null DEFAULT '' #默認是空字符串
);alter table 表名稱 modify 字段名 數(shù)據(jù)類型 default 默認值;
alter table 表名稱 modify 字段名 數(shù)據(jù)類型 default 默認值 not null;ALTER TABLE empx 
MODIFY eid INT DEFAULT 0;ALTER TABLE empx 
MODIFY ename VARCHAR(20) DEFAULT 'van' NOT NULL;ALTER TABLE empx 
MODIFY eid INT;#刪除默認值約束，也不保留非空約束
alter table 表名稱 modify 字段名 數(shù)據(jù)類型 ;
#刪除默認值約束，保留非空約束
alter table 表名稱 modify 字段名 數(shù)據(jù)類型 not null;

8. 小結

面試1、為什么建表時，加 not null default ‘’ 或 default 0
答：不想讓表中出現(xiàn)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支持多種存儲引擎，每一個表都可以指定一個不同的存儲引擎，需要注意的是：外鍵約束是用來保證數(shù)據(jù)的參照完整性的，如果表之間需要關聯(lián)外鍵，卻指定了不同的存儲引擎，那么這些表之間是不能創(chuàng)建外鍵約束的。所以說，存儲引擎的選擇也不完全是隨意的。