MySQL之完整性約束

pure3417發表於2021-05-28
MySql

完整性約束

完整性約束的定義

為了保證插入資料的正確性和合法性，給表中欄位新增，除了資料型別約束以外的其他約束條件。

完整性約束的分類

  • 實體完整性：記錄之間不能重複。

    • 主鍵約束(primary key)：唯一併且不能為空
    • 唯一約束(unique)：唯一可以為空
    • 主鍵自增(auto_increment)

  • 域完整性：資料庫表的欄位，必須符合某種特定的資料型別或約束。

    • 型別約束：在建立表的時候，已經給每個欄位新增型別了
    • 非空約束：not null
    • 預設值：default

  • 引用完整性(參照完整性)：一張表中欄位的值，需要參考另外一張表中的值。

    • 新增外來鍵約束：foreign key
    • 引用完整性會降低sql的執行效率，有時候能不用就不用。

主鍵約束(primary key)

  • 主鍵約束(primary key)：唯一併且不能為空；
  • 一張表只能有一個主鍵欄位，但是可以有聯合主鍵；

新增主鍵約束，有以下3種方式：

方式一：在建立表的同時，新增主鍵約束。
create table student(
    sid int primary key,
    sname varchar(20),
    age int
)charset=utf8;
-- 新增主鍵約束後，插入相同的sid，會報錯。
insert into student(sid,sname,age) values (1,'張三',22);
insert into student(sid,sname,age) values (1,'李四',33);

方式二：在建立表的同時，新增主鍵約束，但是新增方式不同。
  "只有這種新增主鍵的方式，可以新增聯合主鍵"
create table student(
    sid int,
    sname varchar(20),
    age int,
    primary key(sid)
)charset=utf8;

方式三：建立完表後，新增主鍵約束。
create table student(
    sid int,
    sname varchar(20),
    age int
)charset=utf8;                  
alter table student add primary key(sid);
-- constraint表示新增的是一個約束；      
-- pk_sid是給約束起一個名字；

單個主鍵和聯合主鍵的區別

"單個主鍵"
    將表中某一個欄位設定為主鍵，用這一個欄位，來唯一確定一條記錄。
    只要sid唯一不為空，每一行就是一條唯一的記錄。
    primary key(sid)
    sid   sname   age
    1     張三    18
    2     張三    18   

"聯合主鍵"
    將表中2個欄位(或多個欄位)設定為聯合主鍵，用這2個欄位(或多個欄位)，
    來唯一確定一條記錄。
    這2個欄位可以分別重複，只要不是同時重複即可。
    primary key(sid，classid)
    sid   classid   sname   age
    1     2         張三    18
    1     3         張三    18  
    2     4         李四    20
    3     4         王五    30
    -- 下面兩條記錄，聯合欄位同時重複，表示一條重複記錄，插入時報錯。
    5     5         李闖    25  
    5     5         王二    24

主鍵欄位的挑選原則

  1. 通常選擇無意義的欄位作為主鍵欄位，比如說表中記錄每一行行號的id欄位，就是無意義的欄位，很適合作為主鍵

  2. 主鍵欄位一般不會對其進行修改(像欄位名、欄位型別等)

  3. 經常變化的欄位，有意義的欄位，不適合作為主鍵

特別注意：當一個建表語句中，某個欄位只有primary key限制，而沒有使用主鍵自增auto_increament
需要我們自己給主鍵欄位插入值，否則會出現如下錯誤。"ERROR 1364 (HY000): Field 'sid' doesn't have a default value"

但是我們上面說過，主鍵欄位一般是不進行修改的，也就是說不要主動給他值，主鍵欄位應該是讓系
統預設賦值。這個在學習過主鍵自增後你就明白了。

記住：primary key和auto_increament最好配合使用。

舉例說明：

刪除主鍵約束

alter table student drop primary key;

主鍵自增(auto_increment)

主鍵自增的含義和特點

見名知意，這個是用來幫助主鍵自動新增值的一個約束。上面我們說過，主鍵欄位最好不要有任何改動，當然也包括給主鍵欄位插入數值的時候，也不要手動去新增主鍵，而是讓系統自動給主鍵欄位進行賦值。主鍵自增有如下2個特點：

  1. 設定了主鍵自增後，主鍵值會從1開始，每次自增1。
  2. 以前用過的主鍵值，當你刪除該行記錄後，主鍵值仍然是一個新的值，並不會重複使用以前出現過的主鍵值。

對第二個特點的說明：

新增唯一約束，有以下2種方式：

方式一：建立表的時候，在新增主鍵的同時，新增主鍵自增。 
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;  
-- primary key和auto_increment配合使用，插入資料的時候，
-- 不需要給主鍵欄位sid賦值了。
insert into student(sname,age) values ("張三",22),("李四",25)；

方式二：建立完表後，給欄位同時新增"主鍵和主鍵自增"。
create table student(
   sid int,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;  
-- 以下2種方式，均可以給欄位同時新增"主鍵和主鍵自增"。
alter table student modify sid int primary key auto_increment;
alter table student change sid sid int primary key auto_increment;

刪除主鍵自增

alter table student modify sid int primary key;

唯一約束(unique)

唯一性約束的含義

由於一張表中只能由一個主鍵。但是對於某些欄位，像身份證這樣的欄位，肯定也是唯一的，我們不能又將其設定為主鍵(身份證欄位屬於有意義的欄位)，那麼怎麼保證它的唯一性呢？這就是接下來要講述的唯一約束。注意：一張表可以有多個唯一約束。

新增唯一約束，有以下3種方式：

方式一：在建立表的同時，新增唯一約束。
create table student(
    sid int primary key key auto_increment,
    sname varchar(20),
   age int,
   idcard varchar(18) unique
)charset=utf8;  


方式二：在建立表的同時，新增唯一約束，但是新增方式不同。
  "只有這種新增主鍵的方式，可以新增聯合主鍵"
create table student (
    sid int primary key auto_increment,
    sname varchar(20),
    age int,
    idcard varchar(18),
    unique(idcard)
)charset=utf8;  

方式三：建立完表後，新增唯一約束。
create table student (
    sid int primary key auto_increment,
    sname varchar(20),
    age int,
    idcard varchar(18)
)charset=utf8;                      
alter table student add unique(idcard);

-- 設定了唯一約束的欄位，當插入相同的值，會報錯。
insert into student(sname,age,idcard) values ("李四",18,"123456");
insert into student(sname,age,idcard) values ("王五",22,"123456");

刪除唯一約束

刪除唯一約束，使用的語句有點不同。
alter table student drop key idcard;

新增非空約束(not null)

新增非空約束

非空約束指的是我們給某個欄位設定了非空約束後，當我們給該欄位插入空值的時候，會報錯。新增非空約束，有以下2種方式：

方式一：建立表的時候，給欄位新增非空約束。 
create table student(
   sid int primary key auto_increment,
   sname varchar(20) not null,
   age int,
   idcard varchar(18) 
)charset=utf8;

方式二：建立完表後，給欄位新增非空約束。
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;

-- 有以下兩種新增方式。
alter table student modify sname varchar(20) not null;
alter table student change sname sname varchar(20) not null;

欄位新增非空約束後具有如下特點

  1. 欄位設定了非空約束後，你不能插入null值。
  2. 欄位設定了非空約束後，不但不能插入null值，而且還不能不插入值
"這些細節知識，有時候是我們沒有注意到的，因此單獨拿出來說明。"
一：欄位設定了非空約束後，你不能插入null值。
-- 下面這樣插入會報錯。
insert into student(sname,age) values (null,22);

二：欄位設定了非空約束後，不但不能插入null值，而且還不能不插入值。
-- 下面這樣插入也會報錯。
insert into student(age,idcard) values (33,"123456789");

舉例如下：

新增預設值約束(default)

某個欄位當被設定了預設值以後，當我們不為該欄位指定值的時候，就會顯示這個預設值。

新增預設值約束

方式一：建立表的時候，給欄位新增預設值。 
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int default 0,
   idcard varchar(18)
)charset=utf8;

方式二：建立完表後，給欄位新增預設值。
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18)
)charset=utf8;
"不同點，需要特別留意"
-- 使用alter新增預設值的程式碼有點特別。
alter table student alter age set default 0;

刪除預設值

alter table student alter column age drop default;

引用完整性(參照完整性)

什麼是引用完整性？

引用完整性指的是，一張表中欄位的取值，需要參照另一張表中某個欄位的取值。這裡涉及到的就是外來鍵：foreign key。一般情況下，本表中的這個欄位設定的是外來鍵foreign key，參考表中的那個欄位的取值，就需要設定為那個表的主鍵。

參考下圖幫助理解：

特別注意：學生表中的cid參考的是班級表中的cid，可以看到，這裡的欄位名是完全一樣的。但是，實際上，這兩個欄位名可以不一樣，但是這兩個欄位的資料型別必須是一樣的

構成引用完整性的條件：

  1. 一張表（參照表）中的某個欄位，參照另一張表（被參照表）中的欄位（欄位名可以不同）。
  2. 兩張表中的欄位名可以不同，但是欄位的資料型別必須一致。
  3. 參照表中的欄位在該表中是foreign key。被參照表中的欄位在該表中是primary key。

新增外來鍵約束(foreign key)

需要記住一句話：先有被引用的表，然後才可以給當前表新增外來鍵約束，否則，你引用誰去？

新增外來鍵約束，有以下2種方式：

方式一：建立表的時候，給欄位新增外來鍵約束。 
create table classroom(
    cid int primary key auto_increment,
    cname varchar(20) not null
)charset=utf8;

create table student(
   sid int primary key auto_increment,
   sname varchar(20) not null,
   age bit(1),
   cid int,
   constraint fk_cid foreign key(cid) references classroom(cid)
)charset=utf8;

方式二：建立完表後，給欄位新增外來鍵約束。
create table classroom(
    cid int primary key,
    cname varchar(20) not null
)charset=utf8;

create table student(
    sid int primary key auto_increment,
    sname varchar(20) not null,
    age bit(1),
    cid int
)charset=utf8;

alter table student add constraint fk_cid foreign key(cid)
references classroom(cid);
"注意：constraint後面接的是別名，fk_cid相當於起的別名。"

相關文章