General Note: Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database
In this tutorial, you will learn how to use the constraints in SQLite. Constraints are used to force the data before saving.
We have 5 constraints.
NOT NULL – do not allow to store null values in column
UNIQUE – do not allow duplicate values
PRIMARY key – do not allow nulls and duplicates
FOREIGN KEY – it holds primary key column values only
CHECK – used to define our own condition.
There are two types of constraint
- Column level
- Table level
Column constraint syntax:
Table level constraint syntax:
If a constraint is defined after the columns data type they are called as column level constraints.
If a constraint is defined at the end of all columns definition they are called as table level constraints.
Here am going to create a table, which is named as demo_nn, whose id is marked as not null after the column data type. This is called as column level constraint. So the id column won’t allow null values. If you try to insert null value in id column that will throw an error.
Create table demo_nn(id int not null,name text) Insert into demo_nn values (null,’mano’)
(Will throw an error) And at the same time if you skip the id column, it will try to store null value as its default value because we did not mention any default value for id column. So it will take null as its default value. However, it is marked with not null constraint. So the below insert query will throw an error.
Insert into demo_nn(name) values(’Mango’)
Table level constraint is not support for NOT NULL
Unique is used to store unique values in column. It will not allow duplicate values. Here am creating demo_u table in that table id is marked as unique. The unique field allows null but not duplicate values. Before unique am giving a name to the constraint.
Create table demo_u(id int constraint id_unq unique,name text)
(column level constraint)
Create table demo_u(id int,name text, constraint id_unq unique)
(table level constraint)
Insert into demo_u values(2,’raja’);
If you try to insert 2 again in the id field it will throw an error. But allows null. In the below query am trying to insert null.it executes successfully
Insert into demo_u values (null,’raja’);
If you skip the id column it will try to store null value. So this also executes successfully.
Insert into demo_u(name) values(’raja’);
Primary key will not allow nulls and duplicates. It acts like combination of NOT NULL and UNIQUE.
However, primary key has some restrictions. We can use primary for only one in table. We can not apply primary key for more than one column like other constraints. Primary key used to identify each row uniquely.
CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT );
(Column level constraints)
CREATE TABLE artist1( artistid INTEGER, artistname TEXT, PRIMARY KEY(artistid) );
(table level constraints)
INSERT INTO artist values(2,’Rajni’);
If you skip the artist id, it will try to insert next value(one increment with last value). So it try to insert value 3 in artist id column for artist vikram. But the below query fails to execute in other rdbms’s.
INSERT INTO artist(artistname) values(’Vikram’);
(If you skip the artistid, it will automatically add no 3 as id in artist table. Because the last inserted id value is 2) Now see another example. if you run the below code
Insert into artist values(20,’Kamal’);
20 will be the id for artist kamal. Now the last id value is 20. If run the below query the id tries to store next value of last value.
Insert into artist (artistname) values (’Tina Turner’);
So the value 21 is stored.
Default ascending or increment. If you want descending order of auto fill artist id, just use the DESC keyword while defining PRIMARY KEY.
Foreign key has many use cases. It directly depends on primary key.
Say for example, for billing, we will save bill number, total, billed date in one table and the bill have many items included in it. So the orders will be stored in other table but both table linked using primary key and foreign key. Just look at the below table for better understanding.
Why we doing like this. We can store them all in table. This question will arise in every one’s mind. The answer is normalization. We are not going to see normalization deeply here.
|Bill no(primary key)||Total||Date|
|Bill no(foreign key)||Item name||Price|
So foreign key always point to primary key. The foreign key will support duplicates. But it will not allow non-primary key value. If you try to insert the value 2 in the foreign key column of table 2 that will throw an error.
Here am going to create a track table for artist. Here the artist id point to the artist table artist id. (Foreign key is not enabled by default. So you have enable it manually by using the below command Pragma foreign_keys=on; )
CREATE TABLE track( trackid INTEGER, trackname TEXT, artistid INTEGER, FOREIGN KEY(artistid) REFERENCES artist(artistid));
Insert into track values(1,’california’,2) Insert into track values(1,’california’,20)
Here artist id 2 refers 2 rajni and 20 refers to kamal in artist table. They both have same track name. you can add as many as track name for all artist which is present in the artist table.
Check is used to write our own conditions to force the data. In the below query the age must be greater than 14 because it’s marked with check constraint. If you insert 14 or less than 14 it will throw an error.
Create table demo_c(name text not null,age int check(age>14))
Create table demo_c1(name text not null,age int, check(age>14))
(table level) The below query will throw an error
Insert into demo_c values(‘raja’,14);
Multiple constraints for single column:
We can apply more than one constraint to a single column in table. In the below query the age filed is marked with not null and check constraint. If you skip the age column will throw an error because it tries to insert default(null) value. And if you pass 14 or less than 14 .it won’t store because check constraint marked with greater than 14.
Create table demo_c2(name text not null, age int not null check(age>14))