約束
約束(constraint)概述
為什么要約束
為了保證資料完整性
什么是約束
對表中欄位的（強制）限制
約束的分類
- 角度一：欄位個數
單類約束，多列約束
- 角度二：約束的作用范圍 / 定義位置
列級約束：將此約束宣告在欄位的后面
表級約束：在表中所有欄位宣告完，在所有欄位的后面宣告的約束
- 約束的作用（功能）
not null (非空約束)
unique (唯一性約束)
primary key (主鍵約束)
foreign key (外鍵約束)
check (檢查約束)
default (默認值約束)
如何添加約束
CREATE TABLE 添加約束
ALTER TABLE 增加、洗掉約束
如何查看表中約束
select * from information_schema.table_constraints
where table_name ='表的名字';
NOT NULL (非空約束)
限定某個欄位 / 某列的值不能為空
特點
- 默認所有型別的值都可以是NULL，包括INT,FLOAT等
- 非空約束只能出現在表物件的列上，只能某個列單獨非空，不能組合非空（只能列級約束）
- 一個表可以有很多列都分別設定非空
- 字串' '不等于NULL；0也不等于NULL
添加非空約束
CREATE TABLE 添加約束
CREATE TABLE emp2( #建議大寫
id int not null,
name varchar(15) not null,
email varchar(25),
salary decimal(10,2)
);
desc emp2;
#正確
insert into emp2(id,name,email,salary)
values(1,'Tom','[email protected]',3400);
#錯誤
insert into emp2(id,name,email,salary)
values(null,'Tom','[email protected]',3400);
insert into emp2(id,name,email,salary)
values(1,null,'[email protected]',3400);
ALTER TABLE 增加約束
#增加
alter table emp2
modify email varchar(25) not null;
洗掉非空約束
#洗掉
alter table emp2
modify email varchar(25) null;
注：若在之前已經讓要修改的欄位中有null，則要先將null去除
UNIQUE (唯一性約束)
用來限制某個欄位 / 某列的值不能重復
特點
- 同一個表可以有多個約束
- 唯一約束是以某一列的值唯一，也可以是多個列組合的值唯一
- 唯一性約束允許列值為空(可以多次添加null)
- 在創建唯一性約束時，如果不給唯一約束命名，則默認和列同名（若是多列，則和第一個列名相同）
添加唯一約束
單列
CREATE TABLE 添加約束
CREATE TABLE emp3(
id int unique,
name varchar(15),
email varchar(25),
salary decimal(10,2),
#表級約束
constraint uk_emp3_email unique (email)
#約束名的名字
);
desc emp3;
#正確
insert into emp3(id,name,email,salary)
values(1,'Tom','[email protected]',4500);
insert into emp3(id,name,email,salary)
values(2,'Tom1',null,4700);
insert into emp3(id,name,email,salary)
values(3,'Tom2',null,4600);
#錯誤
insert into emp3(id,name,email,salary)
values(1,'Tom3','[email protected]',4500); #id重復
ALTER TABLE 增加約束
#添加
方式一：
alter table emp3
add constraint uk_emp3_sal unique (salary);#和表級約束差一樣
#約束的名字
方式二：
alter table emp3
modify name varchar(15) unique;#和列級約束一樣
多列（復合型唯一性約束）
約束后的欄位中的資料，不能都是一樣，只要有一點不同，就可以執行
CREATE TABLE USER(
id int ,
name varchar(15),
·password· varchar(25),
#表級約束
constraint uk_USER_name_pwd unique (name,`password`)
#約束名的名字
);
#正確
insert into USER
values (1,'Tom','abc');
insert into USER
values (1,'Tom','abcd');
#錯誤
insert into USER
values (1,'Tom','abc'); #
洗掉唯一性約束
- 添加唯一約束的列上會自動創建唯一索引
- 洗掉唯一約束只能通過洗掉唯一索引的方式洗掉
- 洗掉時需要指明唯一索引名，唯一索引名和唯一約束名一樣（見唯一約束的特點）
#洗掉
alter table emp3
drop index 索引名；
PRIMARY KEY（主鍵約束）
用來唯一標識表中的一行記錄（區分不同的行）
特點
- 主鍵約束相當于非空約束+唯一約束，主鍵約束列不可以重復和出現空值
- 一個表最多一個主鍵約束（可以列級，也可以表級）
- 如果是多列約束，這些列也是不能有空值的，且組合的值不可以重復
- MySQL的主鍵名是PRIMARY, 就算自己命名了主鍵名也沒用
- 創建主鍵約束時，系統會建立對應的主鍵索引（主鍵查詢，效率高）
- 不要修改主鍵欄位的值（主鍵是資料記錄的唯一標識），可能會破壞資料的完整性
添加主鍵約束
單列
CREATE TABLE 添加約束
CREATE TABLE emp4(
id int primary key,#列級約束
name varchar(15),
email varchar(25),
salary decimal(10,2)
);
CREATE TABLE emp5(
id int,
name varchar(15),
email varchar(25),
salary decimal(10,2),
constraint pk_emp5_id primary key (id)#表級約束，但是沒有必要起名字
);
#正確
insert into emp4(id,name,email,salary)
values(1,'Tom','[email protected]',3400);
#錯誤
insert into emp4(id,name,email,salary)
values(1,'Tom','[email protected]',3400); #id重復
insert into emp4(id,name,email,salary)
values(null,'Tom','[email protected]',3400); #出現null值
ALTER TABLE 增加約束
ALTER TABLE emp6
add primary key (id);
多列
CREATE TABLE USER1(
id int ,
name varchar(15),
·password· varchar(25),
primary key (name,`password`)
);
洗掉主鍵約束(實際開發中不會洗掉)
alter table emp6
drop primary key;
自增列：AUTO_INCREMENT
讓某個欄位的值自增
特點和要求
- 一個表只能有一個自增長列
- 自增列必須是鍵列（主鍵列，唯一鍵列）
- 自增約束的列型別必須是整型
- 如果自增列指定了0和null，會在當前最大值上自增；如果自增列手動指定了具體值，直接賦值具體值
注：在實際開發中如果主鍵作用的欄位有auto_increment，則在添加資料是不需要對該欄位去賦值
添加自增長列
CREATE TABLE 添加約束
CREATE TABLE emp7(
id int primary key auto_increment ,
name varchar(15)
);
insert into emp7(name)
values('Tom'); #執行一次，id+1不需要賦值
ALTER TABLE 增加約束（一般不會使用）
alter table emp8 #id已經是主鍵
modify id int auto_increment;
洗掉自增列
alter table emp8
modify id int;
MySQL8.0新特性——自增量的持久性
若洗掉一行記錄，重啟服務器，再執行添加陳述句，則該自增列的值=剛才洗掉的行的自增列的數+1
FOREIGN KEY(外鍵約束)
限定某個表的某個欄位的參考完整性
不可以跨存盤引擎
主表和從表 / 父表和子表
- 主表（父表）：被參考的表，被參與的表
- 從表（子表）：參考別人的表，參考別人的表
eg:學生表和課程表是主表，選課表是從表
特點：
- 從表的外鍵列，必須是主表的主鍵列或是唯一約束列（被參考的數要是唯一的）
- 創建外鍵約束時，如果不起名字，默認名不是列名，而是自動產生一個外鍵名
- 創建外鍵時，要先創建主表，再創建從表
- 洗掉時，先洗掉從表（或是外鍵約束），再洗掉主表（要是洗掉其中資料道理也是一樣）
- 在從表中建立外鍵約束，一個表也可建立多個外鍵約束
- 從表的外鍵列和主表的參照列名字可以不同，但是資料型別必須是一樣的
- 當創建外鍵約束時，系統默認在所在的列上建立對應的普通索引，但是索引名是列名，不是外鍵的約束名
- 洗掉外鍵后，必須手動洗掉對應的索引
添加外鍵約束
CREATE TABLE 添加約束
#先創建主表
CREATE TABLE dept1(
dept_id int primary key,#必須給要關聯的列添加主鍵或是唯一約束
dept_name varchar(15)
);
#創建從表
CREATE TABLE emp01(
emp01_id int primary key auto_increment,
emp01_name varchar(15),
department_id int,
#表級約束
constraint fk_emp01_id foreign key(department_id) references dept1(dept_id)
);
#演示外鍵效果
insert into dept1
values (10,'IT');#先創建主表的資料10號部門
INSERT INTO emp01
values (1001,'Tom',10);
#若是洗掉或是更改當前主表中的10號部門，是不可行的
ALTER TABLE 增加約束
#主表
CREATE TABLE dept2(
dept_id int primary key,
dept_name varchar(15)
);
#從表
CREATE TABLE emp02(
emp01_id int primary key auto_increment,
emp01_name varchar(15),
department_id int,
);
alter table emp02
add constraint fk_emp02_deot_id foreign key (department_id) references dept2(dept_id);
約束等級
最好采用ON UPDATE CASADE ON DELETE RESTRICT 的方式，宣告位置在約束條件的后面
洗掉約束
- 先查看約束名和洗掉外鍵約束
select * from information_schema.table_constraints
where table_name ='表的名字';
alter table 從表名
drop foreign key 約束名;
- 查看索引名，并洗掉（只能手動）
show index from 表名;
alter table 從表名
drop index 索引名;
開發場景
外鍵會讓操作受到限制
不建議使用外鍵和級聯，一切外鍵概念必須在應用層解決（經典白學）
CHECK (檢查約束)
檢查某個欄位的值是否符合相應的要求，一般是指值的范圍
MySQL5.7不支持CHECK約束
CREATE TABLE emp10(
id int,
name varchar(15),
salary decimal(10,2) check (salary > 2000)
);
insert into emp10
values (1,'Tom',3000);#成功
insert into emp10
values (2,'Tom1',1500);#失敗
DEFAULT(默認值約束)
給某個欄位 / 某列指定一個默認值，一旦指定以后，在插入資料時，如果此欄位沒有顯示賦值，則賦值為默認值
添加默認值約束
CREATE TABLE 添加約束
CREATE TABLE emp11(
id int,
name varchar(15),
salary decimal(10,2) default 2000
);
insert into emp11
values (1,'Tom',3000);#成功
insert into emp11(id,name)
values (2,'Tom1');#默認添加2000的工資
ALTER TABLE 增加約束
ALTER TABLE emp12
modify salary varchar(8,2) default 2500;
洗掉默認值約束
ALTER TABLE emp12
modify salary varchar(8,2)；#直接去掉（NOT NULL差不多）
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/443498.html
標籤：其他