【如何成為SQL高手】第四關：完整性約束

👨?🎓 博主介紹：
IT邦德，江湖人稱jeames007，10年DBA作業經驗
中國DBA聯盟(ACDU)成員，目前從事DBA及程式編程

SQL對于現在的互聯網公司產研崗位幾乎是一個必備技能，但僅會SQL的話，應該是什么都做不了，
1.如果你是資料分析師，你需要熟練地把自己腦子里的資料和指標需求翻譯成SQL邏輯去查詢資料，進而完成自己的資料分析報告等，你的產出是分析報告，而不是SQL代碼；
2.如果你是數倉工程師（偏應用層），你需要根據業務邏輯去設計模型，撰寫調度任務去產出資料，以供業務人員使用，你的產出是資料模型和表；
3.如果你是演算法工程師，你可能需要用SQL來實作用戶標簽、特征工程等作業，但是這些是為你的模型訓練評估做基礎準備作業，你的產出是可以提升某些指標的演算法模型，

所以，SQL每個人都要用，但是用來衡量產出的并不是SQL本身，你需要用這個工具，去創造其它的價值，
IT邦德老師帶你成為SQL高手，那我們開始吧~

1.約束創建

1.1 表級完整性約束

按照如下表結構創建tb_student資料表，
要求以表級完整性約束方式定義主鍵，并指定主鍵約束名為pk_student

CREATE TABLE tb_student (
 studentNo CHAR(10) NOT NULL,
 studentName VARCHAR(10) NOT NULL,
 sex CHAR(2),
 birthday DATE, 
 native VARCHAR(20),
 nation VARCHAR(20) default '漢',
 classNo CHAR(6),
 constraint pk_student primary key(studentNo)
) engine=InnoDB default charset=gb2312;

1.2 列級完整性約束

按照如下的表結構創建tb_class，
要求：使用InnoDB存盤引擎，gb2312字符集，主鍵約束為列級完整性約束，
唯一約束為表級完整性約束其約束名為uq_class

CREATE TABLE tb_class (
 classNo CHAR(6) PRIMARY KEY NOT NULL,
 className VARCHAR(20) NOT NULL,
 department VARCHAR(20),
 grade ENUM('1','2','3','4'), 
 classNum TINYINT,
 constraint uq_class unique(className)
) engine=InnoDB default charset=gb2312;

1.3 級聯更新洗掉

洗掉資料表tb_student，按照表tb_student結構重新創建tb_student，
在創建的同時建立tb_student到tb_class的外鍵約束
（兩個表相同含義的屬性是classNo，
因此classNo是tb_student的外鍵），
約束名為fk_student，
并定義相應的參照動作，更新操作為級聯（cascade），
洗掉操作為限制(restrict)，資料表引擎為InnoDB，字符集為gb2312

drop table tb_student;

CREATE TABLE tb_student (
 studentNo CHAR(10) NOT NULL,
 studentName VARCHAR(10) NOT NULL,
 sex CHAR(2),
 birthday DATE, 
 native VARCHAR(20),
 nation VARCHAR(20) default '漢',
 classNo CHAR(6),
 constraint fk_student FOREIGN KEY (classNo)
 references tb_class(classNo) on delete restrict on update cascade
) engine=InnoDB default charset=gb2312;

1.4 外鍵

按照如下表結構創建tb_course表，
要求：外鍵名字為fk_course，引擎為InnoDB，默認字符集為gb2312

CREATE TABLE tb_course (
 courseNo CHAR(6) NOT NULL primary key comment '課程號',
 courseName VARCHAR(20) unique not NULL comment '課程名',
 credit DECIMAL(3,1) not NULL comment '學分',
 courseHour TINYINT(2) not NULL comment '課時數', 
 term TINYINT(2) comment '開課學期',
 priorCourse CHAR(6) comment '先修課程',
 constraint fk_course FOREIGN KEY(priorCourse) REFERENCES tb_course(courseNo)
) engine=InnoDB default charset=gb2312;

定義資料表tb_score，表結構如下表所示, 創建對應的外鍵
引擎為InnoDB，默認字符集為gb2312

Alter table tb_student add primary key(studentNo);   --添加主鍵

CREATE TABLE tb_score(
 studentNo CHAR(10) NOT NULL comment '學號',
 courseNo CHAR(6) NOT NULL comment '課程號',
 credit DECIMAL(4,1) not NULL comment '成績',
 constraint fk_score_stuNo FOREIGN KEY(studentNo) REFERENCES tb_student(studentNo),  
 constraint fk_score_courNo FOREIGN KEY(courseNo) REFERENCES tb_course(courseNo),
 constraint pk_score PRIMARY KEY(studentNo,courseNo)
) engine=InnoDB default charset=gb2312;

注意：外鍵約束對應的主鍵（在表里是主鍵才可以）

2.完整性約束更新

洗掉在表tb_score中定義的外鍵約束fk_score_stuNo
alter table tb_score drop foreign key fk_score_stuNo;

洗掉在表tb_student中定義的主鍵約束，
Alter table tb_student drop primary key;

添加主鍵約束，用alter table陳述句在tb_student對studentNo重新添加主鍵，
Alter table tb_student add primary key(studentNo);

添加外鍵約束，用alter table陳述句在tb_score表對studentNo重新添加外鍵，
的主鍵為tb_student表的studentNo，外鍵名稱為fk_score_stuNo，
ALTER TABLE tb_score ADD CONSTRAINT fk_score_stuNo FOREIGN KEY(studentNo) REFERENCES tb_student(studentNo);

3.技能拓展

3.1 約束型別

約束主要完成對資料的檢驗，保證資料庫資料的完整性；如果有相互依賴資料，保證該資料不被洗掉，
常用五類約束：
1. not null：非空約束，指定某列不為空
2. unique： 唯一約束，指定某列或幾列組合的資料不能重復
3. primary key：主鍵約束，指定某列或幾列組合的資料非空且唯一
4. foreign key：外鍵，指定該列記錄屬于主表中的一條記錄，參照另一條資料
5. check：檢查，指定一個運算式，用于檢驗指定資料

大家點贊、收藏、關注、評論啦 👇🏻👇🏻👇🏻微信公眾號👇🏻👇🏻👇🏻