SQLite constraint

NOT NULL constraint

By default, the column can hold NULL values. If you do not want a column has a NULL value, you need to define this constraint on the column designated not allow NULL values ​​in this column.

NULL and no data is not the same, it represents the unknown data.

Examples

For example, the following statement creates a new SQLite table COMPANY, and adds five, including ID, NAME and AGE three designated does not accept NULL values:

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 constraint in the INSERT INTO statement does not provide a specific value for a column with a default value.

Examples

For example, the following statement creates a new SQLite table COMPANY, and added five. Here, SALARY column default is set to 5000.00. So when the INSERT INTO statement does not provide a value for the column, the column will 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 Constraints

UNIQUE constraint prevents the existence of two records have the same value in a particular column. In the COMPANY table, for example, you may want to prevent two or more persons have the same age.

Examples

For example, the following statement creates a new SQLite table COMPANY, and added five. Here, AGE column to UNIQUE, so you can not have two of the same age record:

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 constraint uniquely identifies each record in a database table. In a table can have multiple UNIQUE columns but only one primary key. In the design of the database table, the primary key is very important. A primary key is a unique ID.

We use the primary key to the referenced table rows. By the primary key to foreign key of other tables to create relationships between tables. As the "long-standing coding oversight" in SQLite, the primary key can be NULL, which is the other databases in different places.

A primary key is a field in a table that uniquely identifies each row in the database table / record. The primary key must contain unique values. A primary key column can not have NULL values.

A table can have one primary key, it may consist of one or more fields. When more than one field as the primary key, they are calleda composite key.

If a table on any field defines a primary key, you can not have two records with the same value in these fields.

Examples

We've seen created with ID as the primary key table COMAPNY various examples:

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 constraint is enabled to enter a record you want to check the condition values. If the condition is false, the record violates a constraint, and can not be entered into the table.

Examples

For example, the following table to create a new SQLite COMPANY, and added five. Here, we add a SALARY column CHECK, so wages can not 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)
);

Removing Constraints

SQLite supports a limited subset of ALTER TABLE. In SQLite in, ALTER TABLE command allows the user to rename the table, or add a new column to an existing table. Rename columns, drop one, or to add or delete from a table constraint is impossible.