SQLite 入門教程三好多約束 Constraints

2020-01-31 15:12:26

字體：大中小

來源：轉載

供稿：網友

一、約束 Constraints

在上一篇隨筆的結尾，我提到了約束，但是在那里我把它翻譯成了限定符，不太準確，這里先更正一下，應該翻譯成約束更貼切一點。那么什么是約束呢？

我們在數據庫中存儲數據的時候，有一些數據有明顯的約束條件。比如一所學校關于教師的數據表，其中的字段列可能有如下約束：

年齡 - 至少大于20歲。如果你想錄入一個小于20歲的教師，系統會報錯
國籍 - 默認中國。所謂默認，就是如果你不填寫，系統自動填上默認值
姓名 - 不能為空。每個人都有名字嘛
員工號 - 唯一。這個可不能亂，工資發錯了就麻煩了
上面提到的大于、默認、不能為空、唯一等等，就是數據的約束條件。我們在用 CREATE TABLE 創建表的時候，就應該將每個字段列的約束條件事先說明（如果有的話），以后再往表里輸入數據的時候，系統會自動為我們檢查是否滿足約束條件，如果不滿足系統會報錯。

SQLite 常用約束如下

NOT NULL - 非空
UNIQUE - 唯一
PRIMARY KEY - 主鍵
FOREIGN KEY - 外鍵
CHECK - 條件檢查
DEFAULT - 默認

二、主鍵 PRIMARY KEY

我們還是進入 SQLite 命令行環境，建立一個 test.db 數據庫用來做實驗，如下

復制代碼代碼如下:

myqiao@ubuntu:~/My Documents/db$ sqlite3 test.db
-- Loading resources from /home/myqiao/.sqliterc
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables 
sqlite>  

運行 .tables 命令沒有返回，說明數據庫是空的。如果你的數據庫里面有內容并影響到下面的實驗，你可以用我們上一篇學的 DROP TABLE 來刪除造成影響的表，或者用 ALTER TABLE ... RENAME TO ... 來改名。

--------------------------------------------------------------------------------

下面言歸正轉，我們來說說主鍵 PRIMARY KEY 。

首先，數據表中每一條記錄都有一個主鍵，這就像我們每的身份證號碼、員工號、銀行帳號；反過來也可以說，每一個主鍵對應著一條數據記錄。所以，主鍵必須是唯一的。
其次，一般情況下主鍵同時也是一個索引，所以通過主鍵查找記錄速度比較快。
第三，在關系型數據庫中，一個表的主鍵可以作為另外一個表的外鍵，這樣，這兩個表之間就通過這個鍵建立了關系。
最后，主鍵一般是整數或者字符串，只要保證唯一就行。在 SQLite 中，主鍵如果是整數類型，該列的值可以自動增長。

--------------------------------------------------------------------------------

下面我們來做實驗

復制代碼代碼如下:

sqlite>  
sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text);
sqlite> .tables
Teachers
sqlite> INSERT INTO Teachers(Name) Values('張三');
sqlite> INSERT INTO Teachers(Name) Values('李四');
sqlite> INSERT INTO Teachers(Name) Values('王二麻子');
sqlite> SELECT * FROM Teachers;
Id          Name      
----------  ----------
1           張三    
2           李四    
3           王二麻 
sqlite> INSERT INTO Teachers(Id,Name) Values(2,'孫悟空');
Error: PRIMARY KEY must be unique
sqlite>  

--------------------------------------------------------------------------------

我們先新建了一個 Teachers 表，并設置了兩個字段列，其中 Id 字段列為主鍵列。然后，我們向其中插入三條數據并查詢，反饋一切正常。

注意：在插入前三條數據的時候，命令中并沒有明確指明 Id 的值，系統自動賦值，并且數值自動增長。

插入第四條數據的時候，我給了一個明確的 Id 編號為 2，因為李四的編號已經是 2 了，所以系統提示我錯誤：主鍵必須唯一。

三、默認值 DEFAULT

有一些特別的字段列，在每一條記錄中，他的值基本上都是一樣的。只是在個別情況下才改為別的值，這樣的字段列我們可以給他設一個默認值。

下面我們來做實驗

復制代碼代碼如下:

sqlite>  
sqlite> DROP TABLE Teachers;
sqlite> .tables
sqlite> 
sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text,Country text DEFAULT '中國');
sqlite> .tables
Teachers
sqlite> INSERT INTO Teachers(Name) Values('張三');
sqlite> INSERT INTO Teachers(Name) Values('李四');
sqlite> INSERT INTO Teachers(Name) Values('王二麻子');
sqlite> INSERT INTO Teachers(Name,Country) Values('孫悟空','天庭');
sqlite> SELECT * FROM Teachers;
Id    Name             Country        
----  ---------------  ---------------
1     張三           中國         
2     李四           中國         
3     王二麻子     中國         
4     孫悟空        天庭 
sqlite> 

先把之前的 Teachers 表刪除，然后重新創建。這回 Teachers 表多了一個 Country 字段，并且設置默認值為“中國”，然后我們插入四條數據到 Teachers 表。

前三條數據都沒有明確指明 Country 字段的值，只有第四條數據指明了“孫悟空”的 Country 為“天庭”。

查詢數據，發現前三條數據都填上了默認值，實驗成功。

--------------------------------------------------------------------------------
數據顯示有點走樣，命令 .width 4 15 15 設置的列寬，可以通過 .show 查看，可能是因為中文的原因，所以沒有對齊。

四、非空 NOT NULL

有一些字段我們可能一時不知到該填些什么，同時它也沒設定默認值，當添加數據時，我們把這樣的字段空著不填，系統認為他是 NULL 值。

但是還有另外一類字段，必須被填上數據，如果不填，系統就會報錯。這樣的字段被稱為 NOT NULL 非空字段,需要在定義表的時候事先聲明。

下面我們來做實驗

復制代碼代碼如下:

sqlite>  
sqlite> DROP TABLE Teachers;
sqlite> .tables
sqlite> 
sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text,Age integer NOT NULL,City text);
sqlite> .tables
Teachers
sqlite> INSERT INTO Teachers(Name,Age) Values('Alice',23);
sqlite> INSERT INTO Teachers(Name,Age) Values('Bob',29);
sqlite> INSERT INTO Teachers(id,Name,Age) Values(6,'Jhon',36);
sqlite> SELECT * FROM Teachers;
Id    Name             Age              City           
----  ---------------  ---------------  ---------------
1     Alice            23               NULL           
2     Bob              29               NULL           
6     Jhon             36               NULL           
sqlite> INSERT INTO Teachers(Name) Values('Mary');
Error: Teachers.Age may not be NULL
sqlite> 

還是先刪除舊表，創建新表。

這回 Teachers 表聲明了一個 NOT NULL 字段 Age，同時還有一個可以為 NULL 的字段 City

插入前三條數據都沒有指定 City 的值，查詢可以看到 City 字段全部為空

注意：這里的 NULL 只是對“什么都沒有”的一種顯示形式，可以通過 .nullvalue 命令改為別的形式，具體見第一篇

插入第四條數據時沒有指定 Age 的值，系統就報錯了： Teachers.Age 不能為空

五、唯一 UNIQUE
這一約束很好理解，除了主列以為，還有一些列也不能有重復值。不多說，直接看代碼

復制代碼代碼如下:

sqlite>  
sqlite> DROP TABLE Teachers;
sqlite> .tables
sqlite> 
sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text UNIQUE);
sqlite> .tables
Teachers
sqlite> INSERT INTO Teachers(Name) VALUES('Alice');
sqlite> INSERT INTO Teachers(Name) VALUES('Bob');
sqlite> INSERT INTO Teachers(Name) VALUES('Jane');
sqlite> INSERT INTO Teachers(Name) VALUES('Bob');
Error: column Name is not unique
sqlite> 

這次的 Teachers 表只有 Name 這一列，但是 Name 列不能有重復值?？梢钥吹剑轿覀兊诙尾迦?Bob 時，系統就報錯了。

六、條件檢查 CHECK

某些值必須符合一定的條件才允許存入，這是就需要用到這個 CHECK 約束。

復制代碼代碼如下:

sqlite>  
sqlite> DROP TABLE Teachers;
sqlite> .tables
sqlite> 
sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Age integer CHECK(Age>22));
sqlite> .tables
Teachers
sqlite> INSERT INTO Teachers(Age) VALUES(45);
sqlite> INSERT INTO Teachers(Age) VALUES(33);
sqlite> INSERT INTO Teachers(Age) VALUES(23);
sqlite> INSERT INTO Teachers(Age) VALUES(21);
Error: constraint failed
sqlite> 

Age 字段要求必須大于 22，當插入的數據小于22時，系統報錯。

七、外鍵 FOREIGN KEY

現在，我們的數據庫中已經有 Teachers 表了，假如我們再建立一個 Students 表，要求 Students 表中的每一個學生都對應一個 Teachers 表中的教師。

很簡單，只需要在 Students 表中建立一個 TeacherId 字段，保存對應教師的 Id 號，這樣，學生和教師之間就建立了關系。

--------------------------------------------------------------------------------

問題是：我們有可能給學生存入一個不在 Teachers 表中的 TeacherId 值，而且發現不了這個錯誤。

這種情況下，可以把 Students 表中 TeacherId 字段聲明為一個外鍵，讓它的值對應到 Teachers 表中的 Id 字段上。

這樣，一旦在 Students 表中存入一個不存在的教師 Id ，系統就會報錯。

復制代碼代碼如下:

sqlite> 
sqlite> .tables
Teachers
sqlite> CREATE TABLE Students (Id integer PRIMARY KEY,  TeacherId integer,  FOREIGN KEY(TeacherId) REFERENCES Teachers(id) );
sqlite> .tables
Students  Teachers
sqlite> SELECT * FROM Teachers;
Id    Age            
----  ---------------
1     40             
2     33             
3     23 
sqlite> INSERT INTO Students(TeacherId) VALUES(1);
sqlite> INSERT INTO Students(TeacherId) VALUES(3);
sqlite> INSERT INTO Students(TeacherId) VALUES(9);
sqlite> SELECT * FROM Students;
Id    TeacherId      
----  ---------------
1     1              
2     3              
3     9 
sqlite> 