SQLite 入門教程(三)好多約束 Constraints

SQLite 入門教程(三)好多約束 Constraints

SQLite 入門教程(三)好多約束 Constraints

資料來源:http://www.cnblogs.com/myqiao/archive/2011/07/13/2105550.html

一、約束 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> 
            
這裡建立了 Students 表,並且把 TeacherId 作為外鍵與 Teachers 表的 Id 列相對應。

問題來了:插入的前兩條資料沒問題,因為 Id 編號 1、3 都在 Teachers 表中; 但是數位 9 並不在 Teachers 表中,不但沒有報錯,系統還保存進去了,這是為什麼呢?

據說 SQLite 的外鍵約束預設情況下並不是開啟的,如果你需要這個功能,你可能需要下載原始程式碼版本,設置每個編譯參數,然後重新編譯,這樣你就得到支持外鍵的 SQLite 了。
 

 

 

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *