SQLite 入門教程（三）好多約束 Constraints

2016-04-22 jashliao Comments 0 Comment

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 列相對應。