5 SQL Constraints to Guarantee Good Database Structure

Key Takeaways

• Use SQL constraints to maintain data integrity and ensure uniformity in your database. The NOT NULL constraint forces columns to reject NULL values.
• Implement primary key constraints to ensure uniqueness of values in a column or set of columns. This prevents duplicate records in a database table.
• Foreign key constraints establish relationships between tables and prevent actions that would destroy links between them. They ensure that a record in the child table references the parent table.

A database is essential for many applications, but it can get messy if you don’t have guidelines for storing and processing data.

SQL constraints specify rules for storing data in a table. When you set constraints, the database will throw an error if you try to store data that violates these rules. Constraints help to maintain data integrity and ensure uniformity in your database.

There are several types of SQL constraints that you can use; here are some of the most useful.

1. NOT NULL Constraint

Database columns, by default, accept data with NULL values. NULL essentially means that no value exists. The NOT NULL constraint forces a column to reject NULL values.

This constraint ensures that each column must contain a value. You cannot add a record to the database without supplying data for any columns with the NOT NULL constraint.

Take the example of a Customers table. There are some necessary details about each customer that you want on record, like their name. Add the NOT NULL constraint to the mandatory fields to ensure that customers provide this information.

Here’s an example showing how you can use the NOT NULL constraint in a PostgreSQL database:

 CREATE TABLE Customers (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Mobile_No int NOT NULL,
    Age int
); 

If you try inserting a customer record without the Age field, the database will accept it with no errors:

 INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 0723000000); 

However, if you try inserting a record without the FirstName field, the database will reject it with an error message:

 ERROR: null value in column "firstname" violates not-null constraint
Detail: Failing row contains (123456, Dior, null, 723000000, null). 

2. PRIMARY KEY Constraint

A KEY is a unique attribute set to a column or field that identifies a table’s tuple (or a record) in database systems. A PRIMARY KEY constraint ensures the uniqueness of values in a column or set of columns. It acts as a unique identifier in a row, preventing duplicate records in a database table.

Primary keys contain unique values and cannot contain NULL values. Each SQL database table must have only one primary key. The PRIMARY KEY can have single or multiple columns.

For example, you are creating a database of customer records. You need each customer to enter their ID numbers differently from everybody else. You can apply the primary key constraint to ensure that none of your customers have the same ID number.

The following code shows how you can introduce a primary key constraint in a MySQL database:

 CREATE TABLE Customers (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
   PRIMARY KEY (ID)
); 

The database will not accept that value if a user enters another record with a similar ID. Instead, it will throw an error indicating duplication. The user tries to insert two records with the same ID in the following example:

 INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'John', 'Doe', 35 ); 

INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'Mary', 'Jane', 35 ); 

The database will show an error message:

 Duplicate entry '1' for key 'PRIMARY' 

But if you change the ID of the second customer, the database accepts the entry. Therefore, the primary key ensures no duplicate IDs in your customer records.

3. FOREIGN KEY Constraint

Foreign keys establish relationships between two tables. You can add a foreign key to a field/column in one table that references the primary key in another table.

The table with the primary key is the parent table, while the table with the foreign key is the child table. A record cannot then exist in the child table without a reference to the parent table.

The foreign key constraint prevents actions that would destroy links between tables. For example, you cannot DROP one table if it links to another with a foreign key. You will have to drop both tables at once.

Unlike a primary key, you can duplicate a foreign key and have more than one in a single table. Foreign key values can also be NULL. In the following example, you must use the customer_id to create an order.

 CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

INSERT INTO Customers(customer_id, first_name, last_name)
VALUES (1, 'Christian', 'Dior');

INSERT INTO Orders(order_id, customer_id, order_date)
VALUES (1, 1, '2023-08-07'); 

If you try creating an order without an existing customer_id, the database shows an error message:

 Cannot add or update a child row: a foreign key constraint fails
(`db_9_4ee205c`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY
(`customer_id`) REFERENCES `customers` (`customer_id`)) 

4. UNIQUE Constraint

This constraint ensures that no two rows can have the same values for a specific column. Like primary keys, a unique constraint maintains data integrity and prevents duplicate entries. If you work with a poorly designed database without the UNIQUE constraint, you may end up having to find and delete the duplicates.

Unlike primary keys, you can have many UNIQUE constraints on one table. For example, when creating a Customers table, you may want to have unique IDs and phone numbers. To add such a constraint using MySQL server, use this syntax:

 CREATE TABLE Customers (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Mobile_No BIGINT UNIQUE 
); 

If you insert records with the same mobile number in the database, it will then show an error message.

 INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 254000000 );

INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (7891011, 'Dedan', 'Kimathi', 254000000 ); 

The error message will be something like this:

 Duplicate entry '254000000' for key 'Mobile_No' 

This UNIQUE constraint ensures the database won’t have customers with the same IDs or mobile numbers.

5. CHECK Constraint

The CHECK constraint limits the value range put in a column. Adding a CHECK constraint on a column will allow only specified values for that column. It enforces data integrity by ensuring a user only inserts valid data in a table.

The CHECK constraint must evaluate a value as TRUE or UNKNOWN for each specified row or table entry. If the value is FALSE, the database shows an error message.

For example, in the Customers table, you may want only to serve customers over 18 years. You can add a CHECK constraint to ensure you don’t serve underage customers. You may add the constraint in a PostgreSQL database, as shown in the following code:

 CREATE TABLE Customers (
    ID int NOT NULL,
    Age int CHECK(Age>=18),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Mobile_No BIGINT UNIQUE 
); 

Now if you try to insert a customer’s age below the age of 18:

 INSERT INTO Customers (ID, Age, LastName, FirstName, Mobile_No)
VALUES (123456, 15, 'Dior', 'Christian', 1254000000 ); 

The database will show an error message like this:

 ERROR: new row for relation "customers" violates check constraint
"customers_age_check"
Detail: Failing row contains (123456, 15, Dior, Christian, 1254000000) 

How to Add and Remove SQL Constraints From Databases

SQL constraints are not cast in stone. You can add or remove constraints on existing tables using the ALTER TABLE statement. The ALTER statement allows you to work with constraints to suit your data needs.

There are many more SQL constraints you can learn to design a database to your liking. You can start with the ones listed in this article.