SQLite 约束|极客教程

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

在 SQLite 中，我们具有以下约束：

非空
独特
首要的关键
外键
校验
默认

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 版，并且必须设置外键的编译指示。

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约束将默认值插入到列中。

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

为了演示DEFAULT约束，我们创建了Hotels表。 City列具有默认的“不可用”值。

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