Understanding Sqlite Database Constraints
Introduction to Sqlite Database Constraints
Rules that are put on data columns of a table are termed as constraints. These constraints play the role of limiting the type of data that can be stored in a table. Thus, ensuring the integrity and reliability of the data stored within the database.There are mainly two kinds of constraints- Table Level Constraints and Column Level Constraints. The column level constraints are applied to the columns of a table. On the other hand, the Table level constraints are applied on a complete table.
Common Constraints Available in Sqlite
1. PRIMARY Key
This constraint gives a unique identification to each record of the table. There can exist more than one UNNIQUE constraint, but there exists only one PRIMARY key in the table.The Primary keys function as unique IDs of the database and are thus important in designing a table. They acts as foreign keys when they are used in other tables while creating relations among two or more tables.
A table can only consist of only a single Primary key, which in turn can have single, or multiple fields.
For example, we have created a table named CLASS in which there are 5 columns and single PRIMARY KEY.
2. NOT NULL Constraint
A column, by default, can store NULL values. In case, the user does not want to have a NULL value in the column, the NOT NULL constraint will ensure that a column will not have a null value. Once the constraint is applied on the column, NULL will not be allowed for that column. However, NULL should not be confused with absence of data. It only represents the unknown data in the column.
For example, the below mentioned statements will create a new table with the name SALARY. The table consists of 5 columns of which NAME, AGE and ADDRESS column shave specified that NULL should not be accepted.
3. DEFAULT Constraint
This constraint provides a default value to the column when no value is specified by the INSERT INTO statement.
For example, in the SALARY table, 5 columns are added. The PF column, be default is set to 4000.00. This will turn out to be helpful in case the INSERT INTO statement does not generate the value for this particular column.
4. UNIQUE Constraint
This constraint is used to prevent two records from having the same values in a particular column of the table. Thus, it ensures that every column has a unique value.
For example, in the SALARY table, the user might want to ensure that no two records are having same age.
5. CHECK Constraint
This constraint enables a condition that is used for checking the value that is being entered into the record. If the condition results in a false value, the record is not entered into the table.
For example, in the above mentioned example the condition is put with CHECK constraint that the PF cannot be 0.
Conclusion
Sqlite database constraints are sure shot parameters for restricting the data that is stored within a table in Sqlite database. However, there are times when the tables belonging to Sqlite database are deleted or corrupted somehow. In such cases, the database cannot be viewed via traditional methods.Therefore, in such cases external software like Sqlite Database Viewer can be deployed in order to view the tables. The software recovers the entire elements stored within the Sqlite database. Hence, it is the perfect way to view corrupted or inaccessible Sqlite database tables.
Primary Key and Unique key are Sqlite database constraints, In this tutorial you will know how to create them to uniquely identify a column in a table.
