Database Constraints in SQLite using Python Code by Querying

This article is the continuation of the previous article in which we have seen how to create and query a simple database. In this article, we will learn the following:

Database Constraints in SQLite (PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY)
Querying Database

We will follow the following relational schema to create the database.

Create Database

As we have learnt in the previous article, first of all we will create a database.

import sqlite3
conn = sqlite3.connect('company.sqlite ')

Before creating our tables, let’s see how to define primary and foreign keys in SQLite.

Primary Key in SQLite

A primary key is defined as a column or a combination (group) of columns which is used to uniquely identify a row in a table.

Each table can have only one primary key. The primary key column must not contain NULL values. It means that the primary key column has an implicit NOT NULL constraint.

How to define primary key in SQLite?

There are two ways to define primary key in SQLite:

Column Constraint

If the table has only one column which is to be defined as a primary key, then you can use the PRIMARY KEY column constraint to define the primary key.

CREATE TABLE tableName(
	column_1 NOT NULL DATATYPE PRIMARY KEY,
	...
);

Table Constraint

If the primary key consists of more than 1 columns, then you use the table constraint PRIMARY KEY to define the primary key.

CREATE TABLE tableName(
	column_1 NOT NULL DATATYPE,
	column_2 NOT NULL DATATYPE,
	...
	PRIMARY KEY(column_1, column_2,…)
);

Foreign Key in SQLite

In order to link two or more tables based on column or group of columns in a database, the foreign key constraint is used. It works as a cross-reference between tables because it references the primary key of another table which actually establishes the link between the tables.

How to define foreign key constraint in SQLite?

In order to define the foreign key, we use the FOREIGN KEY constraint.

Syntax

After defining the columns of the table columns we define the foreign key constraint.

CREATE TABLE tableName(
	…
	FOREIGN KEY (column_name) REFERENCES table2_name (column_2_name)
);

NOT NULL Constraint

NOT NULL constraint is used to prevent a column for having NULL value.

column_name dataType NOT NULL,

DEFAULT Constraint

DEFAULT constraint is used to insert a default value in the column if the value is not provided.

Syntax

column_name dataType DEFAULT DEFAULT_VALUE,

CHECK Constraint

CHECK constraint is used to add a condition for value to be inserted. If the inserted value satisfies the condition then the value will be inserted, otherwise, it won’t be inserted.

Syntax

column_name dataType CHECK(condition),

Now that we have seen some of the constraints. Let’s create our tables as described in the schema figure.

Create Tables

First of all, create the table department, which has three attributes, name of the department, department number as primary key, and manager’s ssn (we will add this column later in order to learn how to alter table).

#Before creating table, drop if it exists
cur.execute('DROP TABLE IF EXISTS department')
#Create table department
cur.execute("""CREATE TABLE department
    (
	name TEXT NOT NULL,
 	deptno INTEGER NOT NULL PRIMARY KEY
    )
""");

As you can see in the code above, deptno is the primary key. This is how we define a single column (column level) primary key.

Now, we create an employee table.

#Before creating table, drop if it exists
cur.execute('DROP TABLE IF EXISTS employee')

#Create table department

cur.execute("""CREATE TABLE employee
	(
		name TEXT NOT NULL,
		ssn TEXT NOT NULL PRIMARY KEY,
		bdate TEXT,
		address TEXT,
		salary INTEGER CHECK(salary > 1000),
		dno INTEGER NOT NULL,
		FOREIGN KEY (dno) REFERENCES department (deptno)
	)
   """);

In the code snippet above, two new constraints are added. CHECK constraint is added on salary column. It means that the value of the salary must be greater than 1000. And FOREIGN KEY constraint is added. dno is a foreign key in the table employee which references the department number (deptno) in the department table.

Now, we create the deptlocations table. In deptlocations table, there is a composite primary key, a primary key which is a combination of two or more columns and is defined as a table-level constraint.

#Before creating table, drop if it exists
cur.execute('DROP TABLE IF EXISTS deptlocations')

#Create table deptlocations
cur.execute("""CREATE TABLE deptlocations
	(
		dnumber INTEGER NOT NULL,
		dlocation TEXT NOT NULL,
		PRIMARY KEY (dnumber, dlocation),
		FOREIGN KEY (dnumber) REFERENCES department (deptno)
	)
""");

Now, the rest of the tables are created.

#Before creating table, drop if it exists
cur.execute('DROP TABLE IF EXISTS project')

#Create table project
cur.execute("""CREATE TABLE project
	(
		pname TEXT NOT NULL,
		pnumber INTEGER NOT NULL,
		plocation TEXT NOT NULL,
		dnum INTEGER NOT NULL,
		PRIMARY KEY (pnumber, plocation),
		FOREIGN KEY (dnum) REFERENCES department (deptno)
	)
""");

#Before creating table, drop if it exists
cur.execute('DROP TABLE IF EXISTS works_on')

#Create table works_on
cur.execute("""CREATE TABLE works_on
	(
		essn TEXT NOT NULL,
		pno INTEGER NOT NULL,
		hour INTEGER NOT NULL,
		PRIMARY KEY(essn,pno),
		FOREIGN KEY (essn) REFERENCES project (employee),
		FOREIGN KEY (pno) REFERENCES project (pnumber)
	)
""");

One last thing, now we want to ALTER our department table to add new column mgr_ssn as shown in the schema.

cur.execute("""ALTER TABLE department
          ADD COLUMN mgr_ssn TEXT REFERENCES employee(ssn);
""");

Now that we have created the database using the DDL (Data Definition Language), you can read the previous article (Getting Started with SQLite with Python) to see how to insert and query data.

Previous Part

— Best Python IDE and Code Editors

— Developing Chat Application in Python with Source Code

— Top 5 Python Web Frameworks to Learn