Keys and constraints

Constraints are the rules enforced on a data columns on table which are used to limit the type of data that can go into a table. Constraints ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

Following are commonly used constraints available in SQLite.

NOT NULL Constraint − Ensures that a column cannot have NULL value.

DEFAULT Constraint − Provides a default value for a column when none is specified.

UNIQUE Constraint − Ensures that all values in a column are different.

PRIMARY Key − Uniquely identifies each row/record in a database table.

CHECK Constraint − Ensures that all values in a column satisfies certain conditions.

1. NOT NULL Constraint

By default, a column can hold NULL values. 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.

A NULL is not the same as no data, rather, it represents unknown data.

Example

For example, the following SQLite statement creates a new table called EMPLOYEE and adds five columns, three of which, ID and NAME and AGE, specifies not to accept NULLs.

CREATE TABLE EMPLOYEE (
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

2. DEFAULT Constraint

The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

Example

For example, the following SQLite statement creates a new table called EMPLOYEE  and adds five columns. Here, SALARY column is set to 8000.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 8000.00.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 8000.00
);

3. UNIQUE Constraint

The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having an identical age.

Example

For example, the following SQLite statement creates a new table called EMPLOYEE and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with the same age −

CREATE TABLE EMPLOYEE (
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 8000.00

);


4. CHECK Constraint

CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.

Example

For example, the following SQLite creates a new table called EMPLOYEE and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY Zero.

CREATE TABLE EMPLOYEE (
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);



5. PRIMARY KEY Constraint

A primary key is a column or group of columns used to identify the uniqueness of rows in a table. Each table has one and only one primary key.

SQLite allows you to define primary key in two ways:

First, if the primary key consists of one column, you use the PRIMARY KEY column constraint to define the primary key as follows:

CREATE TABLE table_name(
   column_1 NOT NULL INTEGER PRIMARY KEY,
   ...
);


Second, in case primary key consists of more than 2 columns, you use the PRIMARY KEY table constraint to define the primary as the following statement.

CREATE TABLE table_name(
   column_1 NOT NULL INTEGER,
   column_2 NOT NULL INTEGER,
   ...
   PRIMARY KEY(column_1,column_2,...)
);

In SQL-standard, the primary key column must not contain NULL values. It means that the primary key column has an implicit NOT NULL constraint.

The following statement creates a table named employees with the emp_id column as the primary key.

CREATE TABLE employees(
 emp_id INTEGER PRIMARY KEY,
 emp_name text NOT NULL
);

We can use this query in our program as shown below:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('CREATE TABLE employees( emp_id INTEGER PRIMARY KEY, emp_name text NOT NULL)')

con.commit()

Since  the primary key of the employees table consists of one column, we define the primary key using PRIMARY KEY column constraint.

We can use the PRIMARY KEY table constraint to define the primary key that consists of one column. It works just fine as the following statement:


CREATE TABLE employees(
emp_id integer,
name text NOT NULL,
PRIMARY KEY (emp_id)
);

For table whose primary key consists of more than one column, it is mandatory to use PRIMARY KEY table constraint to define the primary key. See the example below:


CREATE TABLE regional_employees (
 region_id integer NOT NULL,
 emp_id integer NOT NULL,
 PRIMARY KEY (region_id, emp_id),
 FOREIGN KEY (region_id) REFERENCES employees(region_id)
            ON DELETE CASCADE ON UPDATE NO ACTION,
 FOREIGN KEY (emp_id) REFERENCES regional_employees (emp_id)
            ON DELETE CASCADE ON UPDATE NO ACTION
);

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

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).

Here I am ending today's discussion. In the next post we'll focus on some advanced topics. Till we meet next keep practicing and learning Python as Python is easy to learn!