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 数据库支持的约束。