SQLite Constraint
What is Constraint ?
The CONSTRAINTS are an integrity which defines some conditions that restrict the column to contain the true data while inserting or updating or deleting. We can use two types of constraints, that is column level or table level constraint. The column level constraints can be applied only on a specific column where as table level constraints can be applied to the whole table.
The following column constraints that should be enforced when data is inserted:
- NOT NULL
- PRIMARY KEY
- UNIQUE
- CHECK
- FOREIGN KEY
Table of contents
Constraint
The CONSTRAINTS are an integrity which defines some conditions that restrict the column to contain the true data while inserting or updating or deleting. We can use two types of constraints, that is column level or table level constraint. The column level constraints can be applied only on a specific column where as table level constraints can be applied to the whole table.
NOT NULL
A NOT NULL constraint may only be attached to a column definition, not specified as a table constraint. Not surprisingly, a NOT NULL constraint dictates that the associated column may not contain a NULL value. Attempting to set the column value to NULL when inserting a new row or updating an existing one causes a constraint violation.
Example:
sqlite> create table company( ...> com_id text(4) NOT NULL, ...> com_name text(15) NOT NULL);
Now look at the usage of the NOT NULL with CREATE TABLE statement. See the list of commands below:
.
sqlite> INSERT INTO company VALUES("COM1","T S LTD.");
sqlite> SELECT * FROM company;
COM1|T S LTD.
No problem arise at the time of insertion.
sqlite> INSERT INTO company VALUES("COM1","");
sqlite> SELECT * FROM company;
COM1|T S LTD.
COM1|
Here also no problem arise at the time of insertion, though the value of the second column is blank.
sqlite> INSERT INTO company VALUES("COM1",NULL);
Error: NOT NULL constraint failed: company.com_name
But, here in the above we forcefully tried to enter the value of second column NULL a violation of constraint occurred.
PRIMARY KEY
If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. if a PRIMARY KEY clause is specified as a table-constraint, then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. Each row in a table with a primary key must have a unique combination of values in its primary key columns. For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. More than one PRIMARY KEY clause is not possible in a CREATE TABLE statement. The PRIMARY KEY is optional for ordinary tables. According to the SQL standard, PRIMARY KEY should always imply NOT NULL.
Example:
sqlite> create table company( ...> com_id text(4) PRIMARY KEY, ...> com_name text(15) NOT NULL);
Here in the above example com_id is the single column PRIMARY KEY .
The PRAGMA command can be used to see the structure of the table.
sqlite> .mode column sqlite> .header on sqlite> sqlite> PRAGMA table_info(company); cid name type notnull dflt_value pk ---------- ---------- ---------- ---------- ---------- ---------- 0 com_id text(4) 0 1 1 com_name text(15) 1 0
Now see the usage of PRIMARY KEY.
sqlite> INSERT INTO company VALUES(NULL,"T S LTD.");
Here we insert a row and value against column com_id is NULL, and it has been accepted though the com_id is a PRIMARY KEY because SQLite allows NULL values in a PRIMARY KEY column.
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
T S LTD.
Now,
sqlite> INSERT INTO company VALUES("COM1","T S LTD.");
sqlite> INSERT INTO company VALUES("COM2","K M P CORP.");
sqlite> INSERT INTO company VALUES("COM1","T S LTD.");
Error: UNIQUE constraint failed: company.com_id
Here in the above, the first two rows have been inserted, but in the 3rd line, we forcefully tried to enter the value of com_id COM1, which already exists and this is violating the uniqueness of the PRIMARY KEY and the above error message appeared.
It is good practice to use NOT NULL along with the PRIMARY KEY at the time of creation of column level PRIMARY KEY to making a table more secure and stronger.
Now we are going to enforce two PRIMARY KEY in column level PRIMARY KEY constraint, look the result -
sqlite> create table company( ...> com_id text(4) NOT NULL PRIMARY KEY , ...> com_name text(15) NOT NULL PRIMARY KEY); Error: table "company" has more than one primary key
COMPOSITE PRIMARY KEY
Composite PRIMARY KEY is the combination of more than one columns PRIMARY KEY. Here is the example below -
sqlite> CREATE TABLE orders( ...> ord_id text(4) NOT NULL, ...> item_id text(4) NOT NULL, ...> ord_date date, ...> ord_qty integer, ...> cost integer, ...> PRIMARY KEY (item_id,ord_date) );
To see the structure of the table, use the following commands.
sqlite> .mode column sqlite> .header on sqlite> sqlite> PRAGMA table_info(orders); cid name type notnull dflt_value pk ---------- ---------- ---------- ---------- ---------- ---------- 0 ord_id text(4) 1 0 1 item_id text(4) 1 1 2 ord_date date 0 2 3 ord_qty integer 0 0 4 cost integer 0 0
From the above structure of the table shows that item_id and ord_date columns are combinedly treated as a primary key, and this is composite primary key.
Now,
sqlite> INSERT INTO orders VALUES("ORD1","ITM1","9/13/2014",10,300);
sqlite> INSERT INTO orders VALUES("ORD2","ITM2","8/22/2014",12,300);
sqlite> INSERT INTO orders VALUES("ORD3","ITM1","9/13/2014",25,300);
Error: UNIQUE constraint failed: orders.item_id, orders.ord_date
Here in the above the first two rows have been inserted, but in 3rd line, we forcefully tried to enter the value of item_id is "ITM1" and ord_date is "9/13/2014", which combination is already exists and this is violating the uniqueness of the PRIMARY KEY and the above error message appeared.
UNIQUE
A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.
Example:
sqlite> create table company( ...> com_id text(4) PRIMARY KEY NOT NULL UNIQUE, ...> com_name text(15) NOT NULL UNIQUE);
Here in the above example, UNIQUE constraint has added in each column, but can not do in the case of PRIMARY KEY we have seen before.
CHECK
A CHECK constraint may be attached to a column definition to define what is a valid value for a column. At the time of inserting a new row or updating row, check the value for the specific column whether it is violating the condition, and if the condition evaluates false no new row will be inserted or no row will be updated due to the violation of the constraint.
Example:
sqlite> CREATE TABLE item( ...> item_id text(4) NOT NULL UNIQUE PRIMARY KEY, ...> item_desc text(20) NOT NULL, ...> rate integer CHECK (rate>0 AND rate<100));
Here in the above example CHECK constraint have with rate column, mention that rate must be a positive value and rate should not exceed 100 or more .
Now,
sqlite> INSERT INTO item VALUES("ITM1","Pea-n-Nut",15);
sqlite> INSERT INTO item VALUES("ITM2","Cool Pesta",0);
Error: CHECK constraint failed: item
sqlite> INSERT INTO item VALUES("ITM3","Crack-Munch",110);
Error: CHECK constraint failed: item
Here in the first insertion in item table on problem arise but in the second and the third insertion error been occur, because in the second insertion, the value of rate define 0 which is violating the CHECK constraint and in the third insertion, the value of rate is 110 which is also violating the CHECK constraint
FOREIGN KEY
A FOREIGN KEY CONSTRAINT is used to ensure the referential integrity of the data in one table to match values in another table. It may be one column or list of columns which points to the PRIMARY KEY of another table. The only and useful purpose of FOREIGN KEY is, those values will only appear which present in the primary key table. For each row in the referencing table( the table contains the FOREIGN KEY), the foreign key must match an existing primary key in the referenced table(the table contains the PRIMARY KEY).
Example:
sqlite> create table company( ...> com_id text(4) PRIMARY KEY NOT NULL UNIQUE, ...> com_name text(15) NOT NULL); sqlite> CREATE TABLE item( ...> item_id text(4) NOT NULL UNIQUE PRIMARY KEY, ...> item_desc text(20) NOT NULL, ...> rate integer NOT NULL, ...> com_id text(4) NOT NULL, ...> FOREIGN KEY (com_id) REFERENCES company(com_id));
Here in the above example, the com_id is the PRIMARY KEY in company table, i.e. each com_id comes once in the table; and com_id in item table is FOREIGN KEY, and this is reference to the PRIMARY KEY of the company table, that means, which value against com_id column exists in the company table, only those values are eligible to come in the com_id column in item table.
Now, insert the records into two tables, and see-
sqlite> INSERT INTO company VALUES("COM1","T S LTD.");
sqlite> INSERT INTO company VALUES("COM2","K M P CORP.");
sqlite> INSERT INTO company VALUES("COM3","M B D INC.");
sqlite> INSERT INTO company VALUES("COM4","T S LTD.");
After insertion here is the table below.
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 T S LTD.
COM2 K M P CORP
COM3 M B D INC.
COM4 T S LTD.
INSERT INTO item VALUES("ITM1","Pea-n-Nut",15,"COM1");
INSERT INTO item VALUES("ITM2","Cool Pesta",25,"COM3");
INSERT INTO item VALUES("ITM3","Crack-Munch",12,"COM1");
INSERT INTO item VALUES("ITM4","Pepsi",16,"COM2");
INSERT INTO item VALUES("ITM6","Pea-n-Nut",14,"COM2");
INSERT INTO item VALUES("ITM5","Charlie Gold",700,"COM4");
Here we insert 6 rows. Now I am going to insert 7th rows. Look the results -
INSERT INTO item VALUES("ITM7","Charlie Gold",710,"COM6");
No problem arise though "COM6" does not exist in company table. Because foreign key support has not been enabled for the database session, so if you enabled the support by -
sqlite> PRAGMA foreign_keys = ON;
foreign key support will be enabled.
Now we delete the last row from item table by-
DELETE FROM item WHERE com_id='COM6';
The last inserted have been deleted. Now we execute the following command, and see the result-
sqlite> INSERT INTO item VALUES("ITM7","Charlie Gold",710,"COM6");
Error: FOREIGN KEY constraint failed
Here at the time of insertion of 7th rows, a FOREIGN KEY constraint failed error have appeared because of the value "COM6" in the reference column com_id not exists in company table. Here is the table-
sqlite> SELECT * FROM item;
item_id item_desc rate com_id
---------- ---------- ---------- ----------
ITM1 Pea-n-Nut 15 COM1
ITM2 Cool Pesta 25 COM3
ITM3 Crack-Munc 12 COM1
ITM4 Pepsi 16 COM2
ITM6 Pea-n-Nut 14 COM2
ITM5 Charlie Go 700 COM4
COMPOSITE FOREIGN KEY
A COMPOSITE FOREIGN KEY constraint is one where the child and parent keys are both composite keys, that is, two or more columns are combined in both the parent and child table as a primary key and foreign key respectively.
sqlite> create table comp_mast( ...> com_id text(4) , ...> com_name text(15), ...> prod_id text(4), ...> PRIMARY KEY (com_id,prod_id)); sqlite> create table itm_mast( ...> item_id text(4) , ...> item_name text(15), ...> factory text(4), ...> pro_type text(4), ...> pro_cost integer, ...> FOREIGN KEY(factory,pro_type) REFERENCES comp_mast(com_id,prod_id));
ON DELETE ON UPDATE
ON DELETE and ON UPDATE clauses along with FOREIGN KEY are used to configure actions that take place when deleting rows from the parent table (ON DELETE), or modifying the parent key values of existing rows (ON UPDATE). The ON DELETE and ON UPDATE action associated with each foreign key in a database, execute one of such action i.e. "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
NO ACTION: - Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.
RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exist one or more child keys mapped to it.
SET NULL: If the configured action is "SET NULL", then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values.
SET DEFAULT: The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the columns default value instead of NULL.
CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values.
NO ACTION:
Now if we update and delete the record from company table, no effect to seem in child table item, because no action have been set for DELETE and UPDATE records. If an action is not explicitly specified, it defaults to "NO ACTION". Here is the example-
sqlite> CREATE TABLE company(
...> com_id text(4) NOT NULL UNIQUE PRIMARY KEY,
...> com_name text(15) NOT NULL);
sqlite> INSERT INTO company VALUES("COM1","TS LTD.");
sqlite> INSERT INTO company VALUES("COM2","KMP CORP.");
sqlite> INSERT INTO company VALUES("COM3","MBD INC.");
sqlite> INSERT INTO company VALUES("COM4","TS LTD.");
sqlite> CREATE TABLE item(
...> item_id text(4) NOT NULL UNIQUE PRIMARY KEY,
...> item_desc text(20) NOT NULL,
...> rate integer NOT NULL,
...> icom_id TEXT(4) NOT NULL,
...> FOREIGN KEY (icom_id) REFERENCES company(com_id));
sqlite>
sqlite> INSERT INTO item VALUES("ITM1","Pea-n-Nut",15,"COM1");
sqlite> INSERT INTO item VALUES("ITM2","Cool Pesta",25,"COM3");
sqlite> INSERT INTO item VALUES("ITM3","Crack-Munch",12,"COM1");
sqlite> INSERT INTO item VALUES("ITM4","Pepsi",16,"COM2");
sqlite> INSERT INTO item VALUES("ITM6","Pea-n-Nut",14,"COM2");
sqlite> INSERT INTO item VALUES("ITM5","Charlie Gold",700,"COM4");
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 TS LTD.
COM2 KMP CORP.
COM3 MBD INC.
COM4 TS LTD.
sqlite> SELECT * FROM item;
item_id item_desc rate icom_id
---------- ---------- ---------- ----------
ITM1 Pea-n-Nut 15 COM1
ITM2 Cool Pesta 25 COM3
ITM3 Crack-Munc 12 COM1
ITM4 Pepsi 16 COM2
ITM6 Pea-n-Nut 14 COM2
ITM5 Charlie Go 700 COM4
sqlite> UPDATE company SET com_id='COM5' WHERE com_id='COM4';
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 TS LTD.
COM2 KMP CORP.
COM3 MBD INC.
COM5 TS LTD.
sqlite> SELECT * FROM item;
item_id item_desc rate icom_id
---------- ---------- ---------- ----------
ITM1 Pea-n-Nut 15 COM1
ITM2 Cool Pesta 25 COM3
ITM3 Crack-Munc 12 COM1
ITM4 Pepsi 16 COM2
ITM6 Pea-n-Nut 14 COM2
ITM5 Charlie Go 700 COM4 --> Here no change made.
sqlite> DELETE FROM company WHERE com_id='COM2';
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 TS LTD.
COM3 MBD INC.
COM5 TS LTD.
sqlite> SELECT * FROM item;
item_id item_desc rate icom_id
---------- ---------- ---------- ----------
ITM1 Pea-n-Nut 15 COM1
ITM2 Cool Pesta 25 COM3
ITM3 Crack-Munc 12 COM1
ITM4 Pepsi 16 COM2
ITM6 Pea-n-Nut 14 COM2 --> Record deleted from the parent table but not from the child.
CASCADE
But, if defines the ON UPDATE CASCADE and ON DELETE CASCADE along with the FOREIGN KEY, then look what happen in the following examples -
sqlite> create table company(
...> com_id text(4) NOT NULL UNIQUE PRIMARY KEY,
...> com_name text(15) NOT NULL);
sqlite> CREATE TABLE item(
...> item_id text(4) NOT NULL UNIQUE PRIMARY KEY,
...> item_desc text(20) NOT NULL,
...> rate integer NOT NULL,
...> icom_id TEXT(4),
...> FOREIGN KEY (icom_id)
...> REFERENCES company(com_id)
...> ON UPDATE CASCADE ON DELETE CASCADE);
sqlite> INSERT INTO company VALUES("COM1","TS LTD.");
sqlite> INSERT INTO company VALUES("COM2","KMP CORP.");
sqlite> INSERT INTO company VALUES("COM3","MBD INC.");
sqlite> INSERT INTO company VALUES("COM4","TS LTD.");
sqlite> INSERT INTO item VALUES("ITM1","Pea-n-Nut",15,"COM1");
sqlite> INSERT INTO item VALUES("ITM2","Cool Pesta",25,"COM3");
sqlite> INSERT INTO item VALUES("ITM3","Crack-Munch",12,"COM1");
sqlite> INSERT INTO item VALUES("ITM4","Pepsi",16,"COM2");
sqlite> INSERT INTO item VALUES("ITM6","Pea-n-Nut",14,"COM2");
sqlite> INSERT INTO item VALUES("ITM5","Charlie Gold",700,"COM4");
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 TS LTD.
COM2 KMP CORP.
COM3 MBD INC.
COM4 TS LTD.
sqlite> SELECT * FROM item;
item_id item_desc rate icom_id
---------- ---------- ---------- ----------
ITM1 Pea-n-Nut 15 COM1
ITM2 Cool Pesta 25 COM3
ITM3 Crack-Munc 12 COM1
ITM4 Pepsi 16 COM2
ITM6 Pea-n-Nut 14 COM2
ITM5 Charlie Go 700 COM4
sqlite> UPDATE company SET com_id='COM5' WHERE com_id='COM4';
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 TS LTD.
COM2 KMP CORP.
COM3 MBD INC.
COM5 TS LTD.
sqlite> SELECT * FROM item;
item_id item_desc rate icom_id
---------- ---------- ---------- ----------
ITM1 Pea-n-Nut 15 COM1
ITM2 Cool Pesta 25 COM3
ITM3 Crack-Munc 12 COM1
ITM4 Pepsi 16 COM2
ITM6 Pea-n-Nut 14 COM2
ITM5 Charlie Go 700 COM5
Here in the foreign key field 'icom_id', in the child table 'item', will be updated to match the record(s) in the parent table 'company'.
Here com_id in company table updated by 'COM5' as well as the child key i.e. icom_id of mapped rows of chile table, dependant on the
parent key field i.e. com_id have updated by 'COM5'.
sqlite> DELETE FROM company WHERE com_id='COM2';
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 TS LTD.
COM3 MBD INC.
COM5 TS LTD.
sqlite> SELECT * FROM item;
item_id item_desc rate icom_id
---------- ---------- ---------- ----------
ITM1 Pea-n-Nut 15 COM1
ITM2 Cool Pesta 25 COM3
ITM3 Crack-Munc 12 COM1
ITM5 Charlie Go 700 COM5
Here all the rows from parent table will be deleted which matching the condition as well as
each row in the child table item that was associated with the deleted parent key is also deleted.
RESTRICT
sqlite> create table company(
...> com_id text(4) NOT NULL UNIQUE PRIMARY KEY,
...> com_name text(15) NOT NULL);
sqlite> CREATE TABLE item(
...> item_id text(4) NOT NULL UNIQUE PRIMARY KEY,
...> item_desc text(20) NOT NULL,
...> rate integer NOT NULL,
...> icom_id TEXT(4),
...> FOREIGN KEY (icom_id)
...> REFERENCES company(com_id)
...> ON UPDATE RESTRICT ON DELETE RESTRICT);
sqlite> INSERT INTO company VALUES("COM1","TS LTD.");
sqlite> INSERT INTO company VALUES("COM2","KMP CORP.");
sqlite> INSERT INTO company VALUES("COM3","MBD INC.");
sqlite> INSERT INTO company VALUES("COM4","TS LTD.");
sqlite> INSERT INTO item VALUES("ITM1","Pea-n-Nut",15,"COM1");
sqlite> INSERT INTO item VALUES("ITM2","Cool Pesta",25,"COM3");
sqlite> INSERT INTO item VALUES("ITM3","Crack-Munch",12,"COM1");
sqlite> INSERT INTO item VALUES("ITM4","Pepsi",16,"COM2");
sqlite> INSERT INTO item VALUES("ITM6","Pea-n-Nut",14,"COM2");
Here is the tables:
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 TS LTD.
COM2 KMP CORP.
COM3 MBD INC.
COM4 TS LTD.
sqlite> SELECT * FROM item;
item_id item_desc rate icom_id
---------- ---------- ---------- ----------
ITM1 Pea-n-Nut 15 COM1
ITM2 Cool Pesta 25 COM3
ITM3 Crack-Munc 12 COM1
ITM4 Pepsi 16 COM2
ITM6 Pea-n-Nut 14 COM2
sqlite> UPDATE company SET com_id='COM5' WHERE com_id='COM3';
Error: UNIQUE constraint failed: company.com_id
The above statement shows that the application is prohibited from modifying the parent key com_id(COM3) by COM5 when there exists one or more child keys(COM3) mapped to it.
sqlite> UPDATE company SET com_id='COM5' WHERE com_id='COM4';
sqlite> SELECT * FROM company;
com_id com_name
---------- ----------
COM1 TS LTD.
COM2 KMP CORP.
COM3 MBD INC.
COM5 TS LTD.
But this example shows that the modification take place in parent table without disturbing the child table, because no such value for parent key column exists in a child table.
Previous:
Create, Alter, Drop table
Next:
Create, Alter, Drop index
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sqlite/sqlite-constraint.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics