Constraints are rules that are enforced on the data columns of a table. These are used to limit the types of data that can be inserted into the table. This ensures the accuracy and reliability of the data in the database.
Constraints can be at the column or table level. Column-level constraints apply only to columns, and table-level constraints are applied to the entire table.
The following constraints are commonly used in SQLite.
NOT NULL 约束 Make sure that a column cannot have NULL Value.
DEFAULT 约束 Provides a default value for a column when it does not specify a value
UNIQUE 约束 Make sure that all values in a column are different
PRIMARY Key 约束 Uniquely identifies each row / record in the database table
CHECK 约束 The CHECK constraint ensures that all values in a column meet certain conditions.
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.Example
For example, the following SQLite Statement to create a new table COMPANY And added five columns, of which ID , NAME and AGE Three-column specification is not accepted NULL Value:
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.Example
For example, the following SQLite Statement to create a new table COMPANY And added five columns. Here, the SALARY column is set to 5000.00 by default. So when INSERT INTO Statement does not provide a value for the column, the column is 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 );
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.Example
You’ve seen what we created to ID As the primary key COMAPNY Various instances of the table:
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 tableExample
For example, the following SQLite Create a new table COMPANY And added five columns. Here, we work for SALARY Column addition CHECK So the salary cannot be 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) );
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.