SQLite 约束

最后修改于 2020 年 7 月 6 日

在本节 SQLite 教程中，我们将介绍约束。

约束 放置在列上。它们限制了可以插入到表中的数据。

在 SQLite 中，我们有以下约束

• NOT NULL（非空）
• UNIQUE
• PRIMARY KEY（主键）
• FOREIGN KEY（外键）
• CHECK（检查）
• DEFAULT（默认）

SQLite NOT NULL 约束

具有 NOT NULL 约束的列不能有 NULL 值。

sqlite> CREATE TABLE People(Id INTEGER, LastName TEXT NOT NULL, 
   ...> FirstName TEXT NOT NULL, City TEXT);

我们创建了两个带有 NOT NULL 约束的列。

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 约束确保所有数据在列中是唯一的。

sqlite> CREATE TABLE Brands(Id INTEGER, BrandName TEXT UNIQUE);

在这里，我们创建一个名为 Brands 的表。BrandName 列被设置为 UNIQUE。不能有两个品牌具有相同的名称。

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

我们得到一个错误“列 BrandName 不是唯一的”。只能有一个百事品牌。

请注意，PRIMARY KEY 约束自动在其上定义了一个 UNIQUE 约束。

SQLite 主键约束

PRIMARY KEY 约束唯一地标识数据库表中的每个记录。可以有更多 UNIQUE 列，但表中只能有一个主键。主键在设计数据库表时非常重要。主键是唯一的 ID。我们使用它们来引用表行。当在表之间创建关系时，主键成为其他表中的外键。由于一个“长期存在的编码疏忽”，主键在 SQLite 中可以为 NULL。其他数据库则不然。

sqlite> DROP TABLE Brands;
sqlite> CREATE TABLE Brands(Id INTEGER PRIMARY KEY, BrandName TEXT);

Brands 表的 Id 列成为 PRIMARY KEY。

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 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，并且必须设置外键的 pragma 键。

sqlite> PRAGMA foreign_keys=1;

使用 PRAGMA 语句强制执行外键。

sqlite> DELETE FROM Authors WHERE AuthorId=1;
Error: foreign key constraint failed

尝试删除仍在 Books 表中拥有书籍的作者会导致错误。该作者未被删除。

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，这意味着不允许删除或更新。

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)。

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 子句对关系数据库的数据施加有效性约束。在将数据添加到相关列或更新数据时，将执行检查。

sqlite> .schema Orders
CREATE TABLE Orders(Id INTEGER PRIMARY KEY, OrderPrice INTEGER CHECK(OrderPrice>0), 
Customer TEXT);

我们看一下 Orders 表的定义。我们看到对 OrderPrice 列施加了 CHECK 约束。当然，订单的价格必须是一个正值。

sqlite> INSERT INTO Orders(OrderPrice, Customer) VALUES(-10, 'Johnson');
Error: constraint failed

如果我们尝试插入一个无效值，我们会收到一个错误，提示“约束失败”。

SQLite DEFAULT 约束

如果没有任何值可用，DEFAULT 约束会在列中插入一个默认值。

sqlite> CREATE TABLE Hotels(Id INTEGER PRIMARY KEY, Name TEXT, 
   ...> City TEXT DEFAULT 'not available');

为了演示 DEFAULT 约束，我们创建了一个 Hotels 表。City 列有一个默认的“not available”（不可用）值。

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