mysql的約束

標籤：使用   io   ar   for   資料   sp   on   c   ad   

MySQL中約束儲存在information_schema資料庫的table_constraints中，可以通過該表查詢約束資訊；

約束主要完成對資料的檢驗，保證資料庫資料的完整性；如果有相互依賴資料，保證該資料不被刪除。  

常用五類約束：
not null：非空約束，指定某列不為空白
unique： 唯一約束，指定某列和幾列組合的資料不能重複
primary key：主鍵約束，指定某列的資料不能重複、唯一
foreign key：外鍵，指定該列記錄屬於主表中的一條記錄，參照另一條資料
check：檢查，指定一個運算式，用於檢驗指定資料
注意： MySQL不支援check約束，但可以使用check約束，而沒有任何效果；  
根據約束資料列限制，約束可分為：
單列約束：每個約束只約束一列
多列約束：每個約束約束多列資料   

1、not null

非空約束用於確保當前列的值不為空白值，非空約束只能出現在表對象的列上。
Null類型特徵： 所有的類型的值都可以是null，包括int、float等資料類型 Null 字元串””是不等於null，0也不等於null
create table temp(        
id int not null,        
name varchar(255) not null default ‘abc’,        
sex char null
)
上面的table加上了非空約束，也可以用alter來修改或增加非空約束
增加非空約束
alter table temp modify sex varchar(2) not null;  
取消非空約束
alter table temp modify sex varchar(2) null;  
取消非空約束，增加預設值
alter table temp modify sex varchar(2) default ‘abc’;  

2、unique

唯一約束是指定table的列或列組合不能重複，保證資料的唯一性。雖然唯一約束不允許出現重複的值，但是可以為多個null，同一個表可以有多個唯一約束，多個列組合的約束。在建立唯一約束的時候，如果不給唯一約束名稱，就預設和列名相同。 MySQL會給唯一約束的列上預設建立一個唯一索引；
create table temp (        
id int not null,        
name varchar(25),        
password varchar(16),
constraint uk_name_pwd unique(name, password)
);
表示使用者名稱和密碼組合不能重複
添加唯一約束
alter table temp add unique(name, password);
修改唯一性限制式
alter table temp modify name varchar(25) unique;
刪除約束
alter table temp drop index name;

3、primary key

主鍵約束相當於唯一約束+非空約束的組合，主鍵約束列不允許重複，也不允許出現空值；如果的多列組合的主鍵約束， 那麼這些列都不允許為空白值，並且組合的值不允許重複。 每個表最多隻允許一個主鍵，建立主鍵約束可以在列層級建立，也可以在表層級上建立。
MySQL的主鍵名總是PRIMARY， 當建立主鍵約束時，系統預設會在所在的列和列組合上建立對應的唯一索引。
列模式：
create table temp(    
id int primary key,    
name varchar(25)
);  
create table temp2(    
id int not null,    
name varchar(25),    
pwd varchar(15),    
constraint pk_temp_id primary key(id)
);  
組合模式：
create table temp2(    
id int not null,    
name varchar(25),    
pwd varchar(15),    
constraint pk_temp_id primary key(name, pwd)
);
alter刪除主鍵約束
alter table temp drop primary key;  
alter添加主鍵
alter table temp add primary key(name, pwd);  
alter修改列為主鍵
alter table temp modify id int primary key;  
設定主鍵自增
create table temp(        
id int auto_increment primary key,        
name varchar(20),        
pwd varchar(16)
);
auto_increment自增模式，設定自增後在插入資料的時候就不需要給該列插入值了。

4、foreign key

外鍵約束是保證一個或兩個表之間的參照完整性，外鍵是構建於一個表的兩個欄位或是兩個表的兩個欄位之間的參照關係。 也就是說從表的外索引值必須在主表中能找到或者為空白。 當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除資料，需要先刪除從表中依賴該記錄的資料， 然後才可以刪除主表的資料。還有一種就是串聯刪除子表資料。
注意：外鍵約束的參照列，在主表中引用的只能是主鍵或唯一鍵約束的列，假定引用的主表列不是唯一的記錄， 那麼從表引用的資料就不確定記錄的位置。同一個表可以有多個外鍵約束。
建立外鍵約束：
主表
create table classes(        
id int auto_increment primary key,        
name varchar(20)
);
從表
create table student(        
id int auto_increment,        
name varchar(22),        
constraint pk_id primary key(id),        
classes_id int references classes(id)
);  
通常先建主表，然後再建從表，這樣從表的參照引用的表才存在。
表層級建立外鍵約束：
create table student(        
id int auto_increment primary key,        
name varchar(25),                       
classes_id int,        
foreign key(classes_id) references classes(id)
);
上面的建立外鍵的方法沒有指定約束名稱，系統會預設給外鍵約束分配外鍵約束名稱，命名為student_ibfk_n， 其中student是表名，n是當前約束從1開始的整數。  
指定約束名稱：
create table student(        
id int auto_increment primary key,        
name varchar(25),        
classes_id int,               
constraint fk_classes_id foreign key(classes_id) references classes(id)
);  
多列外鍵組合，必須用表層級約束文法：
create table classes(        
id int,        
name varchar(20),        
number int,        
primary key(name, number)
);
create table student(        
id int auto_increment primary key,        
name varchar(20),        
classes_name varchar(20),        
classes_number int,               
foreign key(classes_name, classes_number) references classes(name, number)
);  
刪除外鍵約束：
alter table student drop foreign key student_ibfk_1;
alter table student drop foreign key fk_student_id;  
增加外鍵約束：
alter table student add foreign key(classes_name, classes_number) references classes(name, number);  
自引用、自關聯（遞迴表、樹狀表）
create table tree(        
id int auto_increment primary key,        
name varchar(50),        
parent_id int,        
foreign key(parent_id) references tree(id)
);  
串聯刪除：刪除主表的資料時，關聯的從表資料也刪除，則需要在建立外鍵約束的後面增加on delete cascade 或on delete set null，前者是串聯刪除，後者是將從表的關聯列的值設定為null。
create table student(        
id int auto_increment primary key,        
name varchar(20),        
classes_name varchar(20),        
classes_number int,         /*表層級聯合外鍵*/        
foreign key(classes_name, classes_number) references classes(name, number) on delete cascade
);  

5、check

MySQL可以使用check約束，但check約束對資料驗證沒有任何作用。
create table temp(        
id int auto_increment,        
name varchar(20),        
age int,        
primary key(id),
check(age > 20)
);
上面check約束要求age必須大於20，但沒有任何作用。但是建立table的時候沒有任何錯誤或警告。

mysql的約束