Java物件導向系列[v1.0.0][SQL語法之資料庫約束]

資料庫約束

所有關係型資料庫都支援對資料表使用約束，在表面上是強制執行的資料校驗規則，通過約束可以更好的的保證資料表裡資料的完整性

大部分資料庫支援5中完整性約束

• NOT NULL:非空約束，指定某列不能為空
• UNIQUE:唯一約束，指定某列或者幾列組合不能重複
• PRIMARY KEY:主鍵，指定該列的值可以唯一地標識該條記錄
• FOREIGN KEY:外來鍵，指定該行記錄從屬於主表中的一條記錄，住院哦用於保證參照完整性
• CHECK:檢查，指定一個布林表示式，用於指定對應列的值必須滿足該表示式

• 大部分資料庫都支援以上5種約束，但MySQL不支援CHECK約束，雖然MySQL的SQL語法可以使用CHECK約束，但不會有任何作用
• 雖然約束的作用只是用於保證資料表裡的資料完整性，但約束也是資料庫物件，並被儲存在系統表中，也擁有自己的名字

約束分類

• 單列約束：每個約束只約束一列
• 多列約束：每個約束可以約束多個資料列

• 建立約束，可以在建表的同時為相應的資料列指定約束，也可以建表後，以修改表的方式增加約束
• MySQL使用information_schema資料庫裡的TABLE_CONSTRAINTS表來儲存該資料庫例項中所有的約束資訊

NOT NULL約束

非空約束用於確保指定列不允許為空，非空約束是比較特殊的約束，他只能作為列級約束使用，只能使用列級約束語法定義
SQL中的null不區分大小寫，且所有資料型別的值都可以是null，包括int、float、boolean等資料型別，且空值是空值，0是0，null是null；

create table davieyang_test_table_not_null
(
	davieyang_id int not null,
	davieyang_name varchar(255) default 'YangDaWei' not null,
	davieyang_gender varchar(2) null
);

此外還可以使用alter table增加或刪除非空約束

alter table davieyang_test_table modify davieyang_gender varchar(2) not null;
alter table davieyang_test_table modify davieyang_name varchar(255) null;
alter table davieyang_test_table modify davieyang_name varchar(255) default 'davieyang' null;

UNIQUE 約束

唯一約束用於保證指定列或者指定列組合不允許出現重複值，雖然唯一約束的列不可以出現重複值，但可以出現多個null值(在資料庫中null不等於null)
同一個表內可建多個唯一約束，唯一約束也可由多列組合而成，當為某列建立唯一約束時，MySQL會為該列相應地建立唯一索引，如果不給唯一約束起名，則該唯一約束預設與列名相同
唯一約束既可以使用列級約束語法建立，也可以使用表級約束語法建立，如果需要為多列建組合約束，或者需要為唯一約束指定約束名，則只能用表級約束語法
當建立唯一約束時，MySQL在唯一約束所在列或列組合上建立對應的唯一索引

列級語法建立唯一約束

create table davieyang_unique_test(
	davieyang_id int not null,
	davieyang_name varchar(255) unique
);

表級語法建立唯一約束

為多列組合建立唯一約束，或者自行制定約束名，語法如下

[constraint 約束名] 約束定義

create table davieyang_unique_test2
(
	davieyang_id int not null,
	davieyang_name varchar(255),
	davieyang_description varchar(255),
	unique(davieyang_name),
	constraint davieyang_constraint unique(davieyang_description)
);

create table davieyang_unique_test3(
	davieyang_id int not null,
	davieyang_name varchar(255),
	davieyang_description varchar(255),
	constraint davieyang_constraint unique(davieyang_name, davieyang_description)
);

如程式碼所示，第一個建表約束中要求davieyang_name和davieyang_description 都不能出現重複值，而第二個建表約束則要求兩者的組合不能出現重複

修改和新增唯一約束

alter table davieyang_unique_test4 add unique(davieyang_name, davieyang_description);

alter table davieyang_unique_test5 modify davieyang_name varchar(255) unique;

刪除唯一約束

對於大部分資料庫而言，刪除約束都是在alter table語句後使用drop constraint 約束名語法來完成，但MySQL並不是用這個方式，而是使用drop index 約束名的方式來刪除約束

alter table davieyang_unique_test6 drop index davieyang_constraint

PRIMARY KEY 約束

• 主鍵約束相當於非空約束和唯一約束，也就是說主鍵約束的列既不允許重複，也不允許出現null值，如果對多列組合建立主鍵約束，則多列裡包含的每一列都不能為空，但只要求這些列組合不能重複
• 主鍵列的值可用於唯一地標識表中的一條記錄
• 每個表只能有一個主鍵，但這個主鍵約束可以由墮餓鬼資料列組合而成，主鍵是表中唯一確定一行記錄的欄位或欄位組合
• 建立主鍵約束時可以使用列級語法，也可以用表級語法，如果需要對多個欄位建立組合主鍵約束，則只能用表級語法
• 使用表級語法來建立約束時，可以為該約束指定約束名，但無論是否為該主鍵約束指定約束名，MySQL總會將所有的主鍵約束命名為PRIMARY

MySQL允許在建立主鍵約束時為該約束命名，但這個欄位沒有用，是為了保持與標準SQL相容，大部分資料庫都允許自定義主鍵約束的名字，而且一旦指定了主鍵約束名，則該約束名就是指定的名字

• 當建立主鍵約束時，MySQL在主鍵約束所在列或列組合上建立對應的唯一索引

列級語法建立主鍵約束

create table davieyang_primary_test(
	davieyang_id int primary key,
	davieyang_name varchar(255),
);

表級越發建立主鍵約束

create table davieyang_primary_test2(
	davieyang_id int not null,
	davieyang_name varchar(255),
	davieyang_description varchar(255),
	constraint davieyang_constraint primary key(davieyang_id)
);

create table davieyang_primary_test3(
	davieyang_name varchar(255),
	davieyang_description varchar(255),
	primary key(davieyang_name, davieyang_desctiption)
);

刪除主鍵約束

alter table davieyang_primary_test4
drop primary key;

修改或增加主鍵約束

alter table davieyang_primary_test5
add primary key(davieyang_name, davieyang_description);

alter table davieyang_primary_test6
modify davieyang_name varchar(255) primary key;

很多資料庫都對主鍵支援自增長的特性，指定自增長的列必須是整型，將該列作為主鍵，通常用於設定邏輯主鍵列，該列的值沒有任何實際意義，僅起到標識作用，MySQL使用auto_increment來設定自增長

create table davieyang_primary_test_finally(
	davieyang_id int auto_increment primary key,
	davieyang_name varchar(255),
	davieyang_description varchar(255),
);

FOREIGN KEY 約束

• 外來鍵約束主要用於保證一個或兩個資料表之間的參照完整性，外來鍵是構建與一個表的兩個欄位或者兩個表的兩個欄位之間的參照關係
• 外間保證了相關的兩個欄位的參照關係：子表外間列的值必須在主表被參照lieder值範圍之內或者為空（也可以通過非空約束外來鍵列不允許為空）
• 當主表的記錄被字表記錄參照時，主表記錄不允許被刪除，必須先把子表裡參照該記錄的所有記錄全部刪除後，才可以刪除主表的該記錄；還有一種方式，刪除主表記錄時級聯刪除子表中所有參照該記錄的從表記錄
• 子表外來鍵參照的只能是主表主鍵列或者唯一鍵列，如此才能保證子表記錄可以準確定位到被參照的主表記錄
• 同一個表可以有多個外來鍵

• 外來鍵約束通常用於定義兩個實體之間的一對多、一對一的關聯關係，而對於一對多的關聯關係，通常在多的一端增加外來鍵列
• 對於一對一的關聯關係，則可選擇任意一方來增加外來鍵，增加外來鍵列的表被稱為子表，只要為外來鍵列增加唯一約束就可以表示一對一的關聯關係
• 對於多對多的關聯關係，則需要額外增加一個連結表來記錄他們的關聯關係

建立外來鍵約束同樣可以採用列級約束語法和表級約束語法，如果僅對單獨的資料列建立外來鍵約束，則使用列級約束語法即可，如果需要對多列組合建立外來鍵約束或者需要為外來鍵約束命名，則必須使用表級約束語法

列級語法建立外來鍵約束

create table davieyang_test(
	davieyang_id int auto_increment,
	davieyang_name varchar(255),
	primary key(davieyang_id)
);

create table davieyang_foreign_test(
	davieyang_f_id int auto_increment primary key,
	davieyang_f_name varchar(255),
	davieyang_f_description varchar(255) references davieyang_test(davieyang_id)
)

MySQL支援使用列級約束語法來建立外來鍵約束，但不會生效，MySQL提供這種列級約束語法僅僅為了和標準SQL的相容性

表級語法建立外來鍵約束

create table davieyang_test(
	davieyang_id int auto_increment,
	davieyang_name varchar(255),
	primary key(davieyang_id)
);

create table davieyang_foreign_test(
	davieyang_f_id int auto_increment primary key,
	davieyang_f_name varchar(255),
	davieyang_f_description varchar(255),
	foreign key(davieyang_f_description) references davieyang_test(davieyang_id)
);

使用表級約束語法可以為外來鍵約束指定約束名，如果建立外來鍵沒有指定約束名，則MySQL會把該外來鍵約束命名為table_name_ibfk_n，其中table_name是子表的表明，n是從1開始的整數；如果顯示的指定外來鍵約束的名字，則可以使用constraint來指定

create table davieyang_test2(
	davieyang_id int auto_increment,
	davieyang_name varchar(255),
	primary key(davieyang_id)
);

create table davieyang_foreign_test2(
	davieyang_f_id int auto_increment primary key,
	davieyang_f_name varchar(255),
	davieyang_f_description varchar(255),
	constraint davieyang_foreign_test2_fk foreign key(davieyang_f_description) references davieyang_test2(davieyang_id)
);

多列組合外來鍵約束

create table davieyang_test3(
	davieyang_name varchar(255),
	davieyang_description varchar(255),
	primary key(davieyang_name, davieyang_description)
);

create table davieyang_foreign_test3(
	davieyang_f_id int auto_increment primary key,
	davieyang_f_name varchar(255),
	davieyang_f_name varchar(255),
	davieyang_f_description varchar(255),
	foreign key(davieyang_f_name, davieyang_f_description) references davieyang_test3(davieyang_name, davieyang_description)
);

刪除外來鍵約束

在alter table後增加drop foreign key約束名子句即可刪除外來鍵

alter table davieyang_foreign_test3
drop foreign key davieyang_foreign_test3_ibfk_1;

增加外來鍵約束

alter table davieyang_foreign_test3
add foreign key(davieyang_f_name, davieyang_f_description) references davieyang_test3(davieyang_name, davieyang_description);

自關聯

外來鍵約束不僅可以參照其他表，還可以參照自身，這種參照自身就稱為自關聯

create table davieyang_foreign_test(
	davieyang_id int auto_increment primary key,
	davieyang_name varchar(255),
	davieyang_refer_id int,
	foreign key(davieyang_refer_id) references davieyang_foreign_test(davieyang_id)
);

級聯刪除

如果想定義刪除主表記錄時，子表記錄也隨之刪除，則需要在建立外來鍵約束後新增on delete cascade或者新增on delete set null，第一種是刪除主表記錄時，把參照該主表記錄的子表記錄全部級聯刪除，而第二種則是把參照該主表記錄的子表記錄的外來鍵設為null

create table davieyang_test4(
	davieyang_id int auto_increment,
	davieyang_name varchar(255),
	primary key(davieyang_id)
);

create table davieyang_foreign_test4(
	davieyang_f_id int auto_increment primary key,
	davieyang_f_name varchar(255),
	davieyang_f_description varchar(255),
	foreign key(davieyang_f_description) references davieyang_test4(davieyang_id) on delete cascade
);

CHECK約束

MySQL支援建表時指定CHECK約束，但這個CHECK約束不會有任何作用，建立CHECK約束只需要在建表的列定義後增加check邏輯表示式即可

create table check_test(
	davieyang_id int auto_increment,
	davieyang_name varchar(255),
	davieyang_salary decimal,
	primary key(davieyang_id),
	check(davieyang_salary>0)
);

如果確實需要使MySQL建立的資料表有CHECK約束，甚至更復雜的完整性約束，實際上可以藉助於MySQL的觸發器機制