Sqlite 简明教程

SQLite - Constraints

约束是对表中的数据列强制执行的规则。用于限制可以进入表的数据类型。这确保了数据库中数据的准确性和可靠性。

Constraints are the rules enforced on a data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

约束可以是列级别或表级别的。列级别约束仅适用于一个列，而表级别约束适用于整个表。

Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

以下是 SQLite 中常用的约束。

Following are commonly used constraints available in SQLite.

NOT NULL Constraint − Ensures that a column cannot have NULL value.
*DEFAULT Constraint * − Provides a default value for a column when none is specified.
UNIQUE Constraint − Ensures that all values in a column are different.
PRIMARY Key − Uniquely identifies each row/record in a database table.
CHECK Constraint − Ensures that all values in a column satisfies certain conditions.

NOT NULL Constraint

默认情况下，列可以存储 NULL 值。如果你不希望列有 NULL 值，那么你需要为此列定义这样的约束，指定该列现在不允许 NULL。

By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column.

NULL 与没有数据不同，相反，它表示未知的数据。

A NULL is not the same as no data, rather, it represents unknown data.

Example

例如，以下 SQLite 语句创建一个名为 COMPANY 的新表，并添加五列，其中三列 ID、NAME 和 AGE 指定不接受 NULL。

For example, the following SQLite statement creates a new table called COMPANY and adds five columns, three of which, ID and NAME and AGE, specifies not to accept NULLs.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

DEFAULT Constraint

DEFAULT 约束在 INSERT INTO 语句未提供特定值时，为列提供默认值。

The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

Example

例如，以下 SQLite 语句创建一个名为 COMPANY 的新表，并添加五列。这里，SALARY 列默认设置为 5000.00，因此，如果 INSERT INTO 语句未为此列提供值，那么默认情况下，此列将设置为 5000.00。

For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, SALARY column is set to 5000.00 by default, thus in case INSERT INTO statement does not provide a value for this column, then by default, this column would be set to 5000.00.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

UNIQUE Constraint

UNIQUE 约束防止两条记录在特定列中具有相同的值。例如，在 COMPANY 表中，你可能希望防止两个人或更多人具有相同的年龄。

The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having an identical age.

Example

例如，以下 SQLite 语句创建一个名为 COMPANY 的新表，并添加五列。这里，AGE 列设置为 UNIQUE，因此你不能有两个具有相同年龄的记录 −

For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with the same age −

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

PRIMARY KEY Constraint

PRIMARY KEY 约束唯一标识数据库表中的每条记录。可以有多个 UNIQUE 列，但表中只有一个主键。在设计数据库表时，主键很重要。主键是唯一的 ID。

The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique IDs.

我们使用它们来引用表行。在表之间创建关系时，主键变为其他表中的外键。由于“长期编码疏忽”，SQLite 中的主键可以为 NULL。其他数据库并非如此。

We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a 'longstanding coding oversight', primary keys can be NULL in SQLite. This is not the case with other databases.

主键是表中的一个字段，它唯一标识数据库表中的每一行/记录。主键必须包含唯一值。主键列不能有 NULL 值。

A primary key is a field in a table which uniquely identifies each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

一个表只能有一个主键，它可以由单个或多个字段组成。当多个字段用作主键时，它们被称为 composite key 。

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

如果表在任何字段上定义了主键，那么你不能让两条记录具有该字段的相同值。

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Example

在上面，你已经看到了各种示例，我们在其中创建了 ID 为主键的 COMPANY 表。

You already have seen various examples above where we have created COMPANY table with ID as a primary key.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

CHECK Constraint

CHECK 约束使条件能够检查要输入到记录中的值。如果条件评估为假，则记录违反约束并且不会输入表中。

CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn’t entered into the table.

Example

例如，以下 SQLite 创建了一个名为 COMPANY 的新表，并添加了五列。这里，我们添加了带 SALARY 列的 CHECK，这样你不能让任何 SALARY 为零。

For example, the following SQLite creates a new table called COMPANY and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY Zero.

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

Dropping Constraint

SQLite 支持 ALTER TABLE 的有限子集。SQLite 中的 ALTER TABLE 命令允许用户重命名表或向现有表添加新列。无法重命名列、删除列或添加或删除表的约束。

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.