YouTip

Constraints are rules enforced on data columns in a 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 can be column-level or table-level. Column-level constraints apply only to a column, and table-level constraints are applied to the whole table. The following are commonly used constraints in SQLite. * **NOT NULL Constraint**: Ensures that a column cannot have a 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 Constraint**: Uniquely identifies each row/record in a database table. * **CHECK Constraint**: The CHECK constraint ensures that all values in a column satisfy a specific condition. ## NOT NULL Constraint 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 this constraint on the column, specifying that NULL value is not allowed for that column. NULL is different from no data; it represents unknown data. ### Example For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, the ID, NAME, and AGE columns are specified as NOT NULL, meaning they do not accept NULL values: ```sql CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); ## DEFAULT Constraint The DEFAULT constraint provides a default value for a column when the INSERT INTO statement does not provide a specific value. ### Example For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, the SALARY column is set to a default of 5000.00. So when the INSERT INTO statement does not provide a value for this column, the column will be set to 5000.00. ```sql 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 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 the same age. ### Example For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, the AGE column is set to UNIQUE, so no two records can have the same age: ```sql 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 The PRIMARY KEY constraint uniquely identifies each record in a database table. A table can have multiple UNIQUE columns, but only one primary key. The primary key is very important when designing database tables. The primary key is a unique ID. We use primary keys to reference table rows. Relationships between tables can be created by setting the primary key as a foreign key in other tables. Due to "long-standing encoding 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. Primary keys must contain unique values. A primary key column cannot contain NULL values. A table can have only one primary key, which can consist of one or more 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 no two records can have the same value in those fields. ### Example We have already seen various examples where we created the COMPANY table with ID as the primary key: ```sql CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); ## CHECK Constraint The CHECK constraint enables a condition to check the value being inserted into a record. If the condition evaluates to false, the record violates the constraint and is not entered into the table. ### Example For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, we add a CHECK for the SALARY column, so the salary cannot be zero: ```sql 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 Constraints In SQLite, to drop a constraint from a table, you typically need to use the ALTER TABLE statement and specify the type of constraint to be dropped. **Dropping a Primary Key Constraint:** ```sql ALTER TABLE table_name DROP CONSTRAINT primary_key_name; Here, **table_name** is the name of the table you are operating on, and **primary_key_name** is the name of the primary key constraint to be dropped. **Dropping a Unique Constraint:** ```sql ALTER TABLE table_name DROP CONSTRAINT unique_constraint_name; Similarly, **table_name** is the table name, and **unique_constraint_name** is the name of the unique constraint to be dropped. **Dropping a Foreign Key Constraint:** ```sql ALTER TABLE table_name DROP CONSTRAINT foreign_key_constraint_name; Here, **table_name** is the table name, and **foreign_key_constraint_name** is the name of the foreign key constraint to be dropped.