SQLite 約束 · ZetCode 中文系列教程

# SQLite 約束 > 原文： [http://zetcode.com/db/sqlite/constraints/](http://zetcode.com/db/sqlite/constraints/) 在 SQLite 教程的這一部分中，我們將處理約束。約束被放置在列上。它們限制了可以插入表中的數據。在 SQLite 中，我們具有以下約束： * 非空 * 唯一 * 主鍵 * 外鍵 * 校驗 * 默認 ## SQLite `NOT NULL`約束具有`NOT NULL`約束的列不能具有`NULL`值。 ```sql sqlite> CREATE TABLE People(Id INTEGER, LastName TEXT NOT NULL, ...> FirstName TEXT NOT NULL, City TEXT); ``` 我們創建兩個具有`NOT NULL`約束的列。 ```sql sqlite> INSERT INTO People VALUES(1, 'Hanks', 'Robert', 'New York'); sqlite> INSERT INTO People VALUES(2, NULL, 'Marianne', 'Chicago'); Error: People.LastName may not be NULL ``` 第一個`INSERT`語句成功，而第二個失敗。該錯誤表明`LastName`列可能不是`NULL`。 ## SQLite `UNIQUE`約束 `UNIQUE`約束確保所有數據在列中都是唯一的。 ```sql sqlite> CREATE TABLE Brands(Id INTEGER, BrandName TEXT UNIQUE); ``` 在這里，我們創建一個表`Brands`。 `BrandName`列設置為`UNIQUE`。不能有兩個名稱相同的品牌。 ```sql sqlite> INSERT INTO Brands VALUES(1, 'Coca Cola'); sqlite> INSERT INTO Brands VALUES(2, 'Pepsi'); sqlite> INSERT INTO Brands VALUES(3, 'Pepsi'); Error: column BrandName is not unique ``` 我們收到錯誤消息`column BrandName is not unique`。只能有一個百事可樂品牌。注意，`PRIMARY KEY`約束自動在其上定義了`UNIQUE`約束。 ## SQLite 主鍵約束 `PRIMARY KEY`約束唯一地標識數據庫表中的每個記錄。可以有更多`UNIQUE`列，但一個表中只有一個主鍵。在設計數據庫表時，主鍵很重要。主鍵是唯一的 ID。我們使用它們來引用表行。在表之間創建關系時，主鍵成為其他表中的外鍵。由于“長期的編碼監督”，因此在 SQLite 中主鍵可以為`NULL`。其他數據庫則不是這種情況。 ```sql sqlite> DROP TABLE Brands; sqlite> CREATE TABLE Brands(Id INTEGER PRIMARY KEY, BrandName TEXT); ``` `Brands`表的`Id`列變為`PRIMARY KEY`。 ```sql sqlite> INSERT INTO Brands(BrandName) VALUES('Coca Cola'); sqlite> INSERT INTO Brands(BrandName) VALUES('Pepsi'); sqlite> INSERT INTO Brands(BrandName) VALUES('Sun'); sqlite> INSERT INTO Brands(BrandName) VALUES('Oracle'); sqlite> SELECT * FROM Brands; Id BrandName ---------- ---------- 1 Coca Cola 2 Pepsi 3 Sun 4 Oracle ``` 在 SQLite 中，如果列為`INTEGER`和`PRIMARY KEY`，則該列也會自動遞增。 ## SQLite 外鍵約束一個表中的`FOREIGN KEY`指向另一表中的`PRIMARY KEY`。它是兩個表之間的引用約束。外鍵標識一個（引用）表中的一列或一組列，該列或表引用另一（引用）表中的一列或一組列。 SQLite 文檔將引用表稱為父表，并將引用表稱為子表。父鍵是外鍵約束所引用的父表中的一列或一組列。這通常是（但并非總是）父表的主鍵。子鍵是子表中受外鍵約束約束并包含`REFERENCES`子句的列或列集。我們使用兩個表來演示此約束：`Authors`和`Books`。 ```sql -- SQL for the Authors & Books tables BEGIN TRANSACTION; DROP TABLE IF EXISTS Books; DROP TABLE IF EXISTS Authors; CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name TEXT); INSERT INTO Authors VALUES(1, 'Jane Austen'); INSERT INTO Authors VALUES(2, 'Leo Tolstoy'); INSERT INTO Authors VALUES(3, 'Joseph Heller'); INSERT INTO Authors VALUES(4, 'Charles Dickens'); CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId)); INSERT INTO Books VALUES(1,'Emma',1); INSERT INTO Books VALUES(2,'War and Peace',2); INSERT INTO Books VALUES(3,'Catch XII',3); INSERT INTO Books VALUES(4,'David Copperfield',4); INSERT INTO Books VALUES(5,'Good as Gold',3); INSERT INTO Books VALUES(6,'Anna Karenia',2); COMMIT; ``` 這是用于創建`Books`和`Authors`表的 SQL。 `Books`表的`AuthorId`列具有外鍵約束。它引用`Authors`表的主鍵。在 SQLite 中，默認情況下不強制使用外鍵。要強制使用外鍵，必須使用適當的標志編譯該庫，該庫必須至少為 3.6.19 版，并且必須設置外鍵的編譯指示。 ```sql sqlite> PRAGMA foreign_keys=1; ``` 外鍵通過`PRAGMA`語句強制執行。 ```sql sqlite> DELETE FROM Authors WHERE AuthorId=1; Error: foreign key constraint failed ``` 嘗試刪除仍在`Books`表中有書的作者會導致錯誤。作者未被刪除。 ```sql sqlite> DELETE FROM Books WHERE AuthorId=1; sqlite> DELETE FROM Authors WHERE AuthorId=1; sqlite> SELECT * FROM Authors; AuthorId Name --------------- ------------------ 2 Leo Tolstoy 3 Joseph Heller 4 Charles Dickens ``` 為了刪除作者，我們必須在`Books`表中刪除他的書。可以定義當必須強制執行外部約束時將采取什么措施。默認操作為`RESTRICT`，這表示不允許刪除或更新。 ```sql CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId) ON DELETE CASCADE); ``` 我們修改`Books`表的架構，并在其中添加`ON DELETE CASCADE`操作。此操作意味著該操作將從父表（`Authors`）傳播到子表（`Books`）。 ```sql sqlite> SELECT Name, Title FROM Authors NATURAL JOIN Books; Name Title --------------- ------------------ Jane Austen Emma Leo Tolstoy War and Peace Joseph Heller Catch XII Charles Dickens David Copperfield Joseph Heller Good as Gold Leo Tolstoy Anna Karenia sqlite> DELETE FROM Authors WHERE AuthorId=2; sqlite> SELECT Name, Title FROM Authors NATURAL JOIN Books; Name Title --------------- ------------------ Jane Austen Emma Joseph Heller Catch XII Charles Dickens David Copperfield Joseph Heller Good as Gold ``` 刪除作者也會刪除他的書。 ## SQLite `CHECK`約束 `CHECK`子句對關系數據庫的數據施加了有效性約束。在向相關列添加或更新數據時執行檢查。 ```sql sqlite> .schema Orders CREATE TABLE Orders(Id INTEGER PRIMARY KEY, OrderPrice INTEGER CHECK(OrderPrice>0), Customer TEXT); ``` 我們看一下`Orders`表的定義。我們看到`OrderPrice`列強加了`CHECK`約束。自然，訂單價格必須為正值。 ```sql sqlite> INSERT INTO Orders(OrderPrice, Customer) VALUES(-10, 'Johnson'); Error: constraint failed ``` 如果嘗試插入無效值，則會收到一條錯誤消息，提示`constraint failed`。 ## SQLite 默認約束如果沒有可用值，則`DEFAULT`約束將默認值插入到列中。 ```sql sqlite> CREATE TABLE Hotels(Id INTEGER PRIMARY KEY, Name TEXT, ...> City TEXT DEFAULT 'not available'); ``` 為了演示`DEFAULT`約束，我們創建了`Hotels`表。 `City`列具有默認的`'not available'`值。 ```sql sqlite> INSERT INTO Hotels(Name, City) VALUES('Kyjev', 'Bratislava'); sqlite> INSERT INTO Hotels(Name) VALUES('Slovan'); sqlite> .width 3 8 17 sqlite> SELECT * FROM Hotels; Id Name City --- -------- ----------------- 1 Kyjev Bratislava 2 Slovan not available ``` 在第一個語句中，我們同時提供酒店名稱和城市名稱。在第二個語句中，我們僅提供酒店名稱。 SQLite 將默認值（`'not available'`文本）放在此處。在 SQLite 教程的這一部分中，我們介紹了 SQLite 數據庫支持的約束。