⛓️Constraints

A SQL constraint is a rule for ensuring the correctness of data in a table. Frequently used SQL constraints include:

• NOT NULL – The column value cannot be empty (i.e. cannot contain a null value).

• UNIQUE – The column cannot contain duplicate values (i.e. all values in the column must be different).

• PRIMARY KEY – Each column value must uniquely identify that row in the table.

• FOREIGN KEY - Ensures values in the column match values in a column from another table. This maintains referential integrity between related tables.

• CHECK - Validates values against a logical expression. Ensures values meet certain conditions.

• DEFAULT - Specifies a default value if one is not provided. Provides a default fallback value.

• INDEX - Improves lookup performance on columns by organizing data for faster searches.

First, let’s look at the Clients table, which stores information about Company X’s clients.

Id	FirstName	LastName	DateOfBirth	SocialSecurityNumber	Age	ActiveFlag
…	…	…	…	…	...	...

What rules might we want to impose on the Clients table?

• FirstName, LastName, and DateOfBirth cannot be empty.

• SocialSecurityNumber must store unique values.

• Age must have values greater than or equal to 18.

• ActiveFlag must be ‘Yes’ by default.

To implement these rules, we use SQL constraints.

Examples of SQL Constraints

Let’s go through all the most common SQL constraints one by one. Each of them is explained with an example.

NOT NULL

The NOT NULL constraint guarantees that the column will have a value for each record. In other words, the column will not allow empty values to be stored in it.

Let’s create a table that contains a column with the NOT NULL constraint:

CREATE TABLE Movies (
ReleaseYear INTEGER,
Title VARCHAR(50) NOT NULL
)

If we try to add this record to the Movies table …

INSERT INTO Movies VALUES (null, null)

… we get an error message saying: Column ‘Title’ cannot be null.

The only way to insert a row to the Movies table is to ensure that the value for column Title is not null:

INSERT INTO Movies VALUES (null, 'Titanic')

Please note that there is no constraint set on the column ReleaseYear; it can be null.

The NOT NULL constraint is used on columns required for a particular record entry, such as:

• When we sign up for an email newsletter, we must provide our email address.

• When registering at the hospital, we must provide our date of birth.

These required fields use the NOT NULL constraint to ensure that the user always provides a value.

UNIQUE

The UNIQUE constraint ensures that no two values in a column are the same.

Let’s look at a table that uses the UNIQUE constraint:

CREATE TABLE Person (DateOfBirth DATE,SocialSecurityNumber INTEGER UNIQUE)

Let’s insert the following record into the Person table:

INSERT INTO Person VALUES (DATE '1970-01-01', 123456789)

Now, suppose we run this exact INSERT statement again. We’ll see an error message: Duplicate entry ‘123456789’ for key ‘uniqueconstraint.SocialSecurityNumber’. This is because the column SocialSecurityNumber implements the UNIQUE constraint.

If we change the second INSERT statement to the following ...

INSERT INTO Person VALUES (DATE '1970-01-01', 123456788)

… it will add another record to the Person table.

Please note that the column DateOfBirth does not implement any constraints and can have any date value for any record.

The UNIQUE constraint is used for columns that cannot contain duplicate values. For example, when we are born, each of us receives a social security number that is unique to us. So if a table contains a column that stores social security numbers, this column must use the UNIQUE constraint to avoid two persons having the same number – even if it is inserted by human error.

CHECK

The CHECK constraint checks the condition that follows it, e.g. CHECK Age>21 ensures that each Age column value is greater than 21.

Below is a table that implements the CHECK constraint:

CREATE TABLE Clients (Name VARCHAR(30),Email VARCHAR(40),Age INTEGER CHECK (Age>=18),ActiveFlag VARCHAR(3))

The CHECK constraint guarantees that each value in the Age column is greater than or equal to 18.

Let’s try inserting a record where the Age value is less than 18:

INSERT INTO Clients VALUES (17, ‘Anthony’, ‘[e-mail]’, ‘Yes’)

In the above case, we receive an error message: Check constraint ‘checkconstraint_chk_1’ is violated.

To add a record to the Clients table, we have to change the INSERT statement:

INSERT INTO Clients VALUES (18, ‘Anthony’, ‘[e-mail]’, ‘Yes’)

Now the CHECK constraint is satisfied.

The CHECK constraint can be used to implement custom constraints on a column. For example:

• If a table must store only adults’ data, we could use a CHECK constraint on the Age column: CHECK (Age>=18),

• If a table must store only US citizens’ data, we could use the CHECK constraint on the Country column: CHECK (Country=‘USA’).

DEFAULT

The DEFAULT constraint sets the default value for a column. This value is used when the user doesn’t enter a value in a column.

Let’s see it in action. First, we create a table with two DEFAULT columns:

CREATE TABLE Zoo (AnimalAge INTEGER DEFAULT 0,AnimalName VARCHAR(20) DEFAULT 'Add name')

The default value for AnimalAge is 0 and for AnimalName it’s ‘Add name’. Let’s try inserting a record into the Zoo table:

INSERT INTO Zoo VALUES ()

As we inserted a record with no values provided, the default values are used for both the columns. Let’s check it by running a SELECT statement:

SELECT * FROM Zoo

AnimalAge	AnimalName
0	Add name

Please note that if we use the following INSERT statement ...

INSERT INTO Zoo VALUES (10, ‘Winnie’)

… then the default values will be overridden.

The DEFAULT constraint should be used whenever we want a column to store a value for every record where the user doesn’t provide a value. For example, when adding a new client to the Clients table, the ActiveFlag column could be set to “Yes” by default.

PRIMARY KEY

According to database normal forms, the PRIMARY KEY constraint should be used in every table in a relational database.

The PRIMARY KEY constraint follows these rules:

• There can be only one PRIMARY KEY in a table.

• PRIMARY KEY values uniquely identify each row; thus, all PRIMARY KEY column values must be unique.

• Null values are not allowed in the PRIMARY KEY column.

Let’s look at an example:

CREATE TABLE Fruits (Id INTEGER PRIMARY KEY,Name VARCHAR(20))

In the above example, the column Id is the primary key for the Fruits table. This means that Id must have unique values and cannot have any null values.

FOREIGN KEY

The FOREIGN KEY constraint creates a link with the PRIMARY KEY of another table. The column that implements the FOREIGN KEY constraint references a PRIMARY KEY column from another table; the current table is linked to the second table through this constraint.

Let’s see an example of a FOREIGN KEY constraint being linked to the PRIMARY KEY of another table:

CREATE TABLE FruitStorage (StorageRoomNo INTEGER PRIMARY KEY,Quantity INTEGER,FruitId INTEGER,FOREIGN KEY (FruitId) REFERENCES Fruits(Id))

The Id column from the Fruits table is a foreign key to the FruitStorage table via the column FruitId. These two tables are linked with each other. If any data is inserted in the FOREIGN KEY column that does not correspond to any value in the PRIMARY KEY column, the database will return an error. However, a FOREIGN KEY column will accept null values.

For example, a customer has only one Id, which belongs to the CustomerProfile table. But one customer can make many purchases, which are stored in the PurchaseRecord table. Here, the two tables are constrained by the PRIMARY KEY and FOREIGN KEY dependency relationship (a one-to-many relationship).

The FOREIGN KEY constraint is frequently used to create links between relational database tables.

Using SQL Constraints

When to Use SQL Constraints

There are a number of situations when we should use SQL constraints to ensure data correctness. In this section, I’ll summarize all the SQL constraints that we’ve learned and implement our first real-world example.

Let’s imagine that we have a table called Clients:

Id	FirstName	LastName	DateOfBirth	SocialSecurityNumber	Age	ActiveFlag
…	…	…	…	…	...	...

When this table was created, we set up rules that must be followed by any application trying to manipulate this table’s data. These rules are:

1. The Id column is the PRIMARY KEY. It uniquely identifies each client record and cannot be null.

2. We need the client’s FirstName, LastName, and DateOfBirth; these columns could use the NOT NULL constraint.

3. The SocialSecurityNumber column will store unique values, and for that we can use the UNIQUE constraint.

4. All the clients must be adults, so we need to CHECK that Age >= 18.

5. We’ll assume that all clients are active, so let’s set the default value of the ActiveFlag column to ‘Yes’ using the DEFAULT constraint.

Data Definition Language (DDL) statements create and modify database objects, including tables. These DDL statements allow us to set and later modify constraints. We set the SQL constraints during table creation using the CREATE statement.

Let’s look at the DDL statement that includes all the constraints listed above. All the constraints are marked in red.

CREATE TABLE Clients(

)

CREATE TABLE Clients (Id INTEGER PRIMARY KEY,FirstName VARCHAR(50) NOT NULL,LastName VARCHAR(50) NOT NULL,DateOfBirth INTEGER NOT NULL,SocialSecurityNumber INTEGER UNIQUE,Age INTEGER CHECK (Age>=18),ActiveFlag VARCHAR(3) DEFAULT 'Yes')

To answer the question of when to use SQL constraints, let me say that, in general, we use SQL constraints to impose certain rules on the columns of particular tables. We decide what types of constraints to apply based on the business context of the table and also our common sense.

SQL constraints are generally used to create a database structure and make all the applications that use this database conform to our rules.

Why Use SQL Constraints?

The usage of SQL constraints ensures that:

• Data is correct (appropriate to the column).

• Data is of good quality (no information is missing or of the wrong data type).

• The applications that input the data must conform to the rules/constraints we set.

By using SQL constraints, we save time spent checking that the data has the required correctness. Constraints ensure that if future INSERTs or UPDATEs do not conform to the rules, they will not be added to our database.