CREATE TABLE sample (name BOOLEAN NOT NULL CHECK (name IN (0, 1))) Now let’s see the different examples of Boolean type as follows. SQLite does not have any separate storage class for the Boolean type, so inserted all Boolean values stored as integer values that 0 and 1. In Boolean, it stores only two values so it requires a single bit to store the value. Actually, integer is one of the storage classes in SQLite and specifically it works on integer values. The integer store the numeric value as a signed number it either positive or negative but Boolean type only works on the positive values in form of 0 or 1. It is not possible to rename a column, remove a column, or add or remove constraints from a table.Basically, SQLite does not support the Boolean data type, so instead of Boolean type SQLite uses the integer data type to execute the Boolean expression in the form of true or false here 0 is used for false and 1 for true that we already discussed. The ALTER TABLE command in SQLite allows the user to rename a table or add a new column to an existing table. SQLite supports a limited subset of ALTER TABLE. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY Zero. Exampleįor example, the following SQLite creates a new table called COMPANY and adds five columns. If the condition evaluates to false, the record violates the constraint and isn't entered into the table. You already have seen various examples above where we have created COMPANY table with ID as a primary key.ĬHECK Constraint enables a condition to check the value being entered into a record. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s). When multiple fields are used as a primary key, they are called a composite key. A primary key column cannot have NULL values.Ī table can have only one primary key, which may consist of single or multiple fields. This is not the case with other databases.Ī primary key is a field in a table which uniquely identifies each rows/records in a database table. Due to a 'longstanding coding oversight', primary keys can be NULL in SQLite. Primary keys become foreign keys in other tables, when creating relations among tables. Primary keys are important when designing the database tables. There can be more UNIQUE columns, but only one primary key in a table. The PRIMARY KEY constraint uniquely identifies each record in a database table. Here, AGE column is set to UNIQUE, so that you cannot have two records with the same age − Exampleįor example, the following SQLite statement creates a new table called COMPANY and adds five columns. In the COMPANY table, for example, you might want to prevent two or more people from having an identical age. The UNIQUE Constraint prevents two records from having identical values in a particular column. Here, SALARY column is set to 5000.00 by default, thus in case INSERT INTO statement does not provide a value for this column, then by default, this column would be set to 5000.00. The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. Exampleįor example, the following SQLite statement creates a new table called COMPANY and adds five columns, three of which, ID and NAME and AGE, specifies not to accept NULLs. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column.Ī NULL is not the same as no data, rather, it represents unknown data. PRIMARY Key − Uniquely identifies each row/record in a database table.ĬHECK Constraint − Ensures that all values in a column satisfies certain conditions.īy default, a column can hold NULL values. UNIQUE Constraint − Ensures that all values in a column are different. NOT NULL Constraint − Ensures that a column cannot have NULL value.ĭEFAULT Constraint − Provides a default value for a column when none is specified. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.įollowing are commonly used constraints available in SQLite. This ensures the accuracy and reliability of the data in the database.Ĭonstraints could be column level or table level. These are used to limit the type of data that can go into a table. Constraints are the rules enforced on a data columns on table.
0 Comments
Leave a Reply. |