1.28. SQLite constraint

1.28.1. NOT NULL constraint ¶

By default, columns can be saved NULL Value. If you don’t want a column to have NULL Value, then you need to define this constraint on the column, specifying that it is not allowed on the column NULL Value.

NULL Unlike no data, it represents unknown data.

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

1.28.2. DEFAULT constraint ¶

DEFAULT Constrain in INSERT INTO When the statement does not provide a specific value, provide a default value for the column.

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

1.28.3. UNIQUE constraint ¶

UNIQUE Constraints prevent two records in a particular column from having the same value. In COMPANY Table, for example, you may want to prevent two or more people from having the same age.

1.28.4. Example ¶

For example, the following SQLite Statement to create a new table COMPANY And added five columns. Here, the AGE column is set to UNIQUE So there cannot be two records of 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
);

1.28.5. PRIMARY KEY constraint ¶

PRIMARY KEY The constraint uniquely identifies each record in the database table. There can be multiple in a table UNIQUE Column, but can have only one primary key. When designing database tables, the primary key is important. The primary key is unique. ID .

We use the primary key to reference the rows in the table. You can create relationships between tables by setting the primary key as the foreign key of other tables. Due to the “long-standing existence of coding supervision”, in SQLite The primary key can be NULL Which is different from other databases

A primary key is a field in a table that uniquely identifies each row / record in a database table. The primary key must contain a unique value. Primary key column cannot have NULL Value.

A table can have only one primary key, which can consist of one or more fields. When multiple fields are used as primary keys, they are called 复合键 .

If a table defines a primary key on any field, no two records can have the same value on those fields.

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

1.28.6. CHECK Constraint ¶

CHECK Constraints enable the condition under which a record is entered to check the value. If the condition value is false The record violates the constraint and cannot be entered into the table

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

1.28.7. Delete constraint ¶

SQLite Support ALTER TABLE Is a finite subset of the In SQLite Medium, ALTER TABLE The command allows the user to rename the table or add a new column to the existing table. It is impossible to rename a column, delete a column, or add or remove constraints from a table.