SQL for Beginners

Prerequisites:

This article assumes you are conversant with the concept of relational databases, and database manipulation and understand what Structured Query Language is and its applications. I recommend this freeCodeCamp SQL tutorial if you do not know of these.

What are constraints in SQL?

In everyday life, constraints act as guiding principles, determining what's allowed and what's not. They shape decisions, like financial constraints limiting our choices and affecting our plans. In SQL, constraints play a parallel role, but instead of budgets, they control the data entering our database tables.

Much like rules ensuring our actions align with our goals, SQL constraints apply regulations to the data, safeguarding its quality and consistency. They are the guardians of data integrity, availability, and reliability in your database.

In this article, we'll delve into SQL constraints, exploring how they can be created, understanding the different types of constraints, and their significance in data management. We'll also walk through practical examples to illustrate their implementation and impact on database operations.

How to Create Constraints in SQL

You have mainly two options when creating constraints. First, you can do so while creating a table using the CREATE TABLE command or after creating the table with the ALTER TABLE command.

The syntax when creating the table using CREATE TABLE will look like this:
CREATE TABLE table_name (
column 1, datatype(size), CONSTRAINT NAME
column 2, datatype(size), CONSTRAINT NAME
);

The Syntax when you have created the table and wish to make modifications via the ALTER TABLE command will look like this:
ALTER TABLE table_name ALTER COLUMN column_name datatype(size), CONSTRAINT NAME

A little explanation of the variables in our Syntax above:

table_name: Name of the SQL table you are creating or modifying.
column_name: Name of the data column you are creating or modifying from your database table.
datatype: The data type of the values you want to add to the column example: INT.
size: Maximum length of the values in the column example: VARCHAR(50).
CONSTRAINT NAME: The name of the type of SQL constraint you will be using.

Types of SQL Constraints

Implementation and examples

1. CHECK Constraint
   When you define a check constraint on a column, it specifies a Boolean expression that must evaluate to true for each row in the table. If the condition evaluates to false for any row during an insert or update operation, the database will reject the operation and return an error, preventing invalid data from being stored.

   The basic syntax to add a check constraint to a column during table creation is as follows:
   CREATE TABLE table_name (

   column_name data_type CHECK (condition)

   );

   For example, let's say you have a table called students, and you want to ensure that the age of a student is between 18 and 35 years. You can add a check constraint on the age column:

    CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT CHECK (age >= 18 AND age <= 35)
    );
   

   With this check constraint in place, any attempt to insert or update the age column with a value outside the range of 18 to 65 will result in an error.

   Check constraints are beneficial for maintaining data integrity and preventing invalid data from being entered into the database. They allow you to define business rules and domain-specific constraints directly at the database level, ensuring consistent and accurate data

2. DEFAULT Constraint

   The default constraint in SQL allows you to specify a default value for a column when no explicit value is provided during an INSERT operation. If an INSERT statement does not include a value for a column with a default constraint, the default value specified for that column will be used.

   For example, if you have a table students with a column gender that has a default constraint set to 'Unknown', and you execute an INSERT statement without providing a value for the gender column, the default value 'Unknown' will be automatically inserted.

    CREATE TABLE students (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        age INT,
        gender VARCHAR(10) DEFAULT 'Unknown'
    );
   

   With the above table definition, if you execute an INSERT statement like this:

    INSERT INTO students (id, name, age) VALUES (1, Dede, 25);
   

   The gender column will automatically be assigned the default value 'Unknown' since it wasn't specified explicitly in the INSERT statement.
   Default constraints are useful when you have columns with commonly used or typical values, and you want to ensure consistency across the table when no specific value is provided during data insertion.

In conclusion, SQL constraints are essential tools for maintaining data accuracy and integrity in databases. By enforcing rules and relationships on the data, constraints ensure that only valid and meaningful information is stored. From primary keys to check constraints, each type serves a specific purpose in safeguarding the data. Incorporating constraints in SQL empowers developers to build reliable and efficient databases, contributing to effective data management and decision-making.

I hope you found this article insightful and valuable in your journey with databases and data management. If you have any further questions or wish to connect, feel free to reach out to me on LinkedIn at It's Your Data Fairy Godmother