Description
By using SQLite Constraints, different number of conditions can be applied on a column. Constraints setup the RDBMS and execute the integrity of the database automatically without need to create triggers and rules. SQLite Constraints are utilized to limit the type of information that can be inserted into a table.
SQLite Constraints can be classified into the following types.They are:
- Unique Constraint
- NOT Null Constraints
- Primary Key Constraints
- Check Constraints
Description
By defining a column with unique constraint, then that unique constraint column will not accept duplicate values. i.e, unique constraint will accept a single character or number. It will not accept repeated values in the table.
Syntax
create table <table_name>(<column_name1> datatype(size) constraint type, <column_name2> datatype(size));
table_name => Any accurate table in the database.
column_name => The operation that can be perform on a column in the table.
constraint_type => constraint type will specify the type of constraint, that is going to be executed in the database.
Examples
By viewing the below example, the concept of unique constraint understands easily.
[sql]
sqlite> Create table employee26(emp_id int,e_name varchar2(20),fee int);
sqlite> Create table employee2(emp_id int unique,e_name varchar2(20),fee int);
sqlite> insert into employee2 values(1,'John',10000);
sqlite> insert into employee2 values(2,'Kelly',20000);
sqlite> insert into employee2 values(2,'Mike',30000);
Error: UNIQUE constraint failed: employee2.emp_id
[/sql]
In the above example, the unique constraint is applied to column_name emp_id in which only unique emp_id's values will be accepted, and duplicate emp_id values will not be accepted.
Description
By defining a column with not null constraint then that constraint column won't acknowledge null values.
Syntax
create table( datatype(size)constraint_type, datatype(size);
table_name => Any accurate table.
column_name => The operation that can be perform on a column in the table.
constraint_type => constraint type will specify the type of constraint, that is going to be executed in the database. Examples
By viewing the below example, the concept of not null constraint easily understands.
[sql]
sqlite> create table employee3(emp_id int not null,e_name varchar2(20)not null,f
ree int);
sqlite> insert into employee3 values(100,'Mike',12000);
sqlite> insert into employee3 values(Null,'Jack',13000);
Error: NOT NULL constraint failed: employee3.emp_id[/sql]
In the above example if not null constraint is applied on a column_name emp_id, then that column will accepts the values other than null values.
Description
By defining a column with primary key constraint then that primary key constraint won't acknowledge invalid and copy values. And a table can contain only one primary key.
Primary key=Unique+Not null
Unique constraints can acknowledge null values where as primary key won't acknowledge null and copy values.
Syntax
create table <table_name> (<column_name1>data type(size) constraint type, <column_name2>data type(size),constraint(column_name1);
table_name => Any accurate table.
column_name => The operation that can be performed in the column of a table
constraint_type => constraint type will specify the type of constraint, that is going to be executed in the database.
Examples
By viewing the below example, the concept of primary key constraint easily understands.
[sql]
sqlite> create table prod11(prod_id int not null,prod_name varchar(255)not null,primary key(prod_id));
sqlite> insert into prod11 values(1,'Mobile');
sqlite> insert into prod11 values(2,'Laptops');
sqlite> insert into prod11 values(3,'System');
sqlite> select * from prod11;
1|Mobile
2|Laptops
3|System
[/sql]
In the above example, the column prod_id is given not null and at the same time in another column prod_id is given primary key constraint. In such case no need to insert each an every value, just writing the prod_name it will automatically display the prod_id values.
Description
A check constraint column specifies a condition to be true or not for all the row in the database table. If the case is said to be untrue, then that statement will be diminished.
The check constraints advantages is the capacity to accomplish explicit integrity guidelines and regulations. For instance, check constraints contains the following rules under employee bench.
Each employee salary should be less than 15000.The commission values must be less than the salary values in the database table.
Syntax
Alter table ADD constraint constraint type CHECK column_name condition;Alter =>This command will alter the database table.
table_name =>The accurate table in the database.
constraint type =>constraint type will specify the type of constraint, that is going to be executed in the database. Key Points
- SQLite Constraints - Different number of conditions can be applied on a column with constraints.
- Unique Constraint - Unique constraint will not accept duplicate values.
- Not Null constraint - Not Null constraint will not accept Null values.
- Primary key constraint - Primary key constraint won't accept null and copied values.
- Check constraints allows only true values and roll_back if the condition is false.