Join Our 4-Week Free Gen AI Course with select Programs.

Request a callback

or Chat with us on

Constraints in SQL: Ensuring Data Integrity and Accuracy

Basics of Python
Basics of Python
icon
5 Hrs. duration
icon
9 Modules
icon
1800+ Learners
logo
Start Learning

In simpler words, SQL constraints are rules we set up to ensure that our data is clean and reliable. This keeps your database tidy, accurate, and trustworthy. Think of constraints as database guidelines. They prevent you from inserting data that violates the rules.

 

For example, if every new user in our database should have an email address, a constraint can make sure nobody fails to do it. And again, we can also use constraints to prevent user instances from submitting the same email twice.

 

However, when we talk about the constraints in SQL, we are referring to building blocks that make a database highly reliable and secure.

 

From tracking customer information to maintaining student grades, the data will be intact with the help of the constraints.

 

Let’s now break down these rules a bit more and see why they are so crucial for all the databases.

Why Constraints are the Most Important Thing in Maintaining Data Integrity and Accuracy

Why do we actually need constraints? It’s easy: unless our data is constrained, it will get too messy.

 

Constraints ensure the data placed into our tables follows the rules we establish. They prevent unwanted duplicate entries, missing information, or incorrect data from being entered.

 

Imagine managing a table of employees. You don’t want two employees with the same employee ID, do you? Similarly, you want each employee to be assigned a name and a valid department.

 

SQL constraints allow you to implement checks on this information so that it always makes sense.

 

So, what do constraints do for us?

 

  • Stop Duplicate Entries: Unique constraints make sure no entries are the same.
  • Enforce Valid Data: The CHECK constraint verifies data against particular conditions.
  • Maintain Relationships: In preserving relationships, foreign key constraints hyperlink tables with their relationship.
  • Make Sure Data Exists: NOT NULL ensures essential columns are not left blank.

 

These are little rules that help avoid big headaches later on. They prevent data corruption, so our systems behave exactly as we expect.

 

The nice thing here is that constraints can be enforced at the time of table creation or later, even as we play with our data.

 

Also Check:  our Free Sql Tutorial

Column-Level vs Table-Level Constraints: What’s the Difference and When to Use What?

There are two kinds of SQL constraints in general: column-level and table-level. This is important to know so that we can apply them correctly.

 

Column-Level Constraints are applied directly to a column.

 

If we only want to put a rule on one specific field, like making sure a student’s name is always entered, we use a column-level constraint.

 

Here’s an example:

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, grade INT );

In this case, the NOT NULL constraint is only applied to the name column. It means every student must have a name, but it does not affect other fields.

 

Table-Level Constraints vary because they apply to multiple columns or to the whole table. We use it when we need to check more than one field together. For example, if we had to be sure that one combination of a student’s ID and class number must be unique.

CREATE TABLE classes ( class_id INT, student_id INT, class_name VARCHAR(50), PRIMARY KEY (class_id, student_id) );

In this scenario, PRIMARY KEY is applied to the columns class_id and student_id. It would prevent a student from appearing multiple times in the same class.

 

Which one do we use for what?

 

  • Use column-level constraints when you only need to enforce one column, like having an employee uniquely identified with a unique ID.
  • Use table-level constraints when the rule is something like preventing duplicate entries across two columns.

 

Understanding these kinds of restrictions will give our database the precise amount of power we want from it.

 

Such rules prevent problems from ever arising in the first place and allow us to be confident that our data is accurate and useful.

Comprehensive Breakdown of Constraints in SQL and Their Practical Applications

NOT NULL Constraint for Preventing Null Values in Essential Fields

We don’t want empty spaces where key data should be, right?

 

This is where the NOT NULL constraint shines. It ensures that critical fields like names, emails, or IDs are always filled. You’d use this when a field is absolutely necessary for every entry.

 

Example:

 

Let’s say we’re creating a students table, and we want to make sure every student has a name.

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT );

Now, when someone tries to insert a student without a name, SQL won’t allow it. It throws an error, making sure we don’t miss out on important data.

UNIQUE Constraint for Ensuring Data Uniqueness Across Columns

Duplicate data is a nightmare. Imagine having two users with the same email in a system.

 

That’s why the UNIQUE constraint is crucial. It makes sure that no two rows have the same value in a column. Here’s how we enforce unique phone numbers in a contacts table:

CREATE TABLE contacts ( contact_id INT PRIMARY KEY, phone_number VARCHAR(15) UNIQUE, name VARCHAR(100) );

In this case, no two contacts can have the same phone number. This ensures each entry remains distinct and avoids data conflicts.

PRIMARY KEY Constraint for Uniquely Identifying Records

The PRIMARY KEY is a bit of a superstar in databases. It’s a combination of NOT NULL and UNIQUE, meaning no two rows can have the same value, and it must always be filled in.

 

Every table should have a primary key to identify records clearly. For instance, let’s say we’re working with a students table where each student needs a unique ID.

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50), age INT );

This ensures that each student has a unique ID, making it easy to reference or update specific records later.

FOREIGN KEY Constraint for Creating Relationships Between Tables

When we need to link tables together, FOREIGN KEY constraints are the way to go. They establish relationships between two tables, ensuring data consistency across them.

 

Let’s say we want to link the departments table with an employees table. The FOREIGN KEY will make sure each employee belongs to a valid department.

CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(50) );   CREATE TABLE employees ( employee_id INT PRIMARY KEY, department_id INT, FOREIGN KEY (department_id) REFERENCES departments(department_id) );

Now, an employee can’t be assigned to a department that doesn’t exist. This helps prevent orphaned data and keeps our relationships clear.

 

Also Read: Difference Between Primary Key and Foreign Key

CHECK Constraint for Validating Data Based on Custom Conditions

What if we need to enforce specific conditions on our data?

 

For that, we use the CHECK constraint. This allows us to add custom rules to make sure data fits within certain limits.

 

Example:

 

In an employees table, we might want to ensure that all salaries are above a certain amount.

CREATE TABLE employees ( employee_id INT PRIMARY KEY, salary DECIMAL(10,2) CHECK (salary > 10000) );

With this rule in place, any attempt to insert a salary below 10,000 will fail. It’s a simple way to keep our data clean and valid.

DEFAULT Constraint for Automatically Assigning Default Values

Let’s face it—sometimes users forget to fill in data. The DEFAULT constraint comes to the rescue by filling in a default value when none is provided.

 

Suppose we’re creating a customers table, and we want the country field to default to ‘India’ if no country is entered.

CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(50), country VARCHAR(50) DEFAULT 'India' );

Now, if we insert a customer without specifying a country, SQL will automatically set it to ‘India.’ This keeps our data consistent without much effort.

INDEX Constraint for Optimising Data Retrieval Speed

Sometimes, searching through large tables can slow down performance.

 

That’s where the INDEX constraint steps in. It speeds up data retrieval by creating indexes on columns we search frequently.

 

Let’s create an index on the last_name column in a students table to make searches faster.

CREATE INDEX idx_last_name ON students(last_name);

Now, queries that search for students by their last name will run much faster. It’s like adding shortcuts to our data, helping us find what we need in no time.

How to Add and Modify SQL Constraints During and After Table Creation

Sometimes, we need to add constraints after a table has been created. Or maybe we want to modify existing constraints.

 

Let’s see how we can handle these scenarios.

Adding Constraints During Table Creation

When we create a table, it’s easy to add constraints directly in the CREATE TABLE statement. We’ve already seen examples where we define constraints like PRIMARY KEY, UNIQUE, and NOT NULL.

 

Here’s a quick recap:

CREATE TABLE employees ( employee_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE, name VARCHAR(50) NOT NULL );

In this case, we’re adding the PRIMARY KEY, UNIQUE, and NOT NULL constraints right from the start. Everything is in place when the table is created.

Adding or Modifying Constraints After Table Creation

But what if we forgot to add a constraint? Or maybe we need to change an existing one?

 

No worries—SQL lets us add or modify constraints later using the ALTER TABLE statement.

 

Example 1: Adding a NOT NULL Constraint

 

Let’s say we forgot to add a NOT NULL constraint to the email column.

ALTER TABLE employees MODIFY email VARCHAR(100) NOT NULL;

This ensures that the email field now requires a value for every employee.

Example 2: Adding a Foreign Key After Table Creation

What if we want to add a FOREIGN KEY to an existing table?

ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);

Now, we’ve added a FOREIGN KEY to the employees table, linking it to the departments table.

Practical SQL Examples Demonstrating the Use of Constraints

Let’s take a step further and look at how these constraints in SQL play out in real-world scenarios. These examples will show you exactly what happens when we apply constraints to our data.

NOT NULL Constraint: Ensuring Critical Fields Are Filled

The NOT NULL constraint forces us to fill in necessary data.

 

Imagine a users table where every user must have a name. We apply the NOT NULL constraint to the name column:

CREATE TABLE users ( user_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(100) );

Now, if we try to insert a user without a name:

INSERT INTO users (user_id, email) VALUES (1, 'raj@example.com');

Output:

user_id name email
Error Field ‘name’ cannot be null

 

This ensures every user has a name, keeping your data clean and useful.

UNIQUE Constraint: Preventing Duplicate Entries in Data

Let’s say we have a contacts table. We don’t want two contacts with the same phone number, right? The UNIQUE constraint is the perfect solution:

CREATE TABLE contacts ( contact_id INT PRIMARY KEY, phone_number VARCHAR(15) UNIQUE, name VARCHAR(100) );

When we try to insert duplicate phone numbers, SQL throws an error:

INSERT INTO contacts (contact_id, phone_number, name) VALUES (1, '9876543210', 'Raj'); -- Trying to insert a duplicate number INSERT INTO contacts (contact_id, phone_number, name) VALUES (2, '9876543210', 'Amit');

Output:

contact_id phone_number name
Error Duplicate entry for ‘9876543210’  

 

This rule keeps each phone number unique, preventing confusion.

PRIMARY KEY Constraint: Establishing Unique Identifiers for Records

Every table needs a way to identify its rows. The PRIMARY KEY does exactly that, combining NOT NULL and UNIQUE. For example, we’re building a students table where each student must have a unique student_id.

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50), grade INT );

Now, SQL won’t allow us to have two students with the same student_id.

INSERT INTO students (student_id, name, grade) VALUES (1, 'Sita', 85); -- Trying to insert the same ID again INSERT INTO students (student_id, name, grade) VALUES (1, 'Gita', 90);

Output:

student_id name grade
Error Duplicate entry for ‘student_id’ 1

 

The PRIMARY KEY guarantees each student’s ID is unique, so we never mix up records.

FOREIGN KEY Constraint: Maintaining Referential Integrity Between Tables

The FOREIGN KEY helps link related data in different tables.

 

For example, we have a departments table and an employees table. Each employee must belong to a valid department.

CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(50) );   CREATE TABLE employees ( employee_id INT PRIMARY KEY, department_id INT, FOREIGN KEY (department_id) REFERENCES departments(department_id) );

Now, if we try to insert an employee with a non-existent department, SQL will stop us:

INSERT INTO employees (employee_id, department_id) VALUES (1, 999);  -- There's no department with ID 999

Output:

employee_id department_id
Error Foreign key constraint fails

 

The FOREIGN KEY keeps our tables consistent, ensuring that employees are always linked to valid departments.

CHECK Constraint: Enforcing Logical Data Validation Rules

The CHECK constraint allows us to set logical conditions on our data. For example, we want to make sure all employees have a salary greater than 10,000.

CREATE TABLE employees ( employee_id INT PRIMARY KEY, salary DECIMAL(10,2) CHECK (salary > 10000) );   Now, let’s try to insert a lower salary:   INSERT INTO employees (employee_id, salary) VALUES (1, 5000);

Output:

employee_id salary
Error Salary must be greater than 10,000

 

The CHECK constraint ensures only valid salaries are entered.

DEFAULT Constraint: Simplifying Data Entry with Predefined Values

With the DEFAULT constraint, we can set default values for columns. For instance, if no country is specified for a customer, SQL will automatically set it to ‘India’.

CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(50), country VARCHAR(50) DEFAULT 'India' );   Now, if we don’t specify a country:   INSERT INTO customers (customer_id, name) VALUES (1, 'Amit');

Output:

customer_id name country
1 Amit India

 

The DEFAULT constraint fills in missing data with the values we want.

INDEX Constraint: Improving Query Performance by Indexing Key Columns

Finally, the INDEX constraint speeds up queries by creating an index on frequently searched columns.

CREATE INDEX idx_last_name ON students(last_name);

This helps SQL find data quickly when we search by last_name, making our queries more efficient.

DevOps & Cloud Engineering
Internship Assurance
DevOps & Cloud Engineering

Common Errors with Constraints in SQL and How to Resolve Them

Even with the best constraints, errors happen. Let’s explore some of the most common constraints in SQL errors and how we can fix them.

NOT NULL Constraint Violation

This happens when we forget to fill in a column marked as NOT NULL. The fix? Always provide value.

INSERT INTO users (user_id, email) VALUES (1, NULL);

This will fail because ’email’ is NOT NULL

 

Solution: Make sure the required field is never left blank.

UNIQUE Constraint Violation

A UNIQUE constraint stops us from entering duplicate values. If we accidentally try to insert a duplicate, SQL will throw an error.

INSERT INTO contacts (contact_id, phone_number, name) VALUES (2, '9876543210', 'Amit');

This fails because the phone number already exists

 

Solution: Check for duplicates before inserting new data.

PRIMARY KEY Constraint Violation

This error happens when we try to insert two rows with the same PRIMARY KEY value.

INSERT INTO students (student_id, name, grade) VALUES (1, 'Gita', 90);

This fails because student_id 1 already exists

 

Solution: Always make sure the PRIMARY KEY is unique.

FOREIGN KEY Constraint Violation

A FOREIGN KEY error occurs when we try to link data that doesn’t exist in the parent table.
The fix is simple—always make sure the parent data exists first.

INSERT INTO employees (employee_id, department_id) VALUES (1, 999);

This fails because there’s no department with ID 999

 

Solution: Make sure the foreign key value exists in the parent table before inserting.

CHECK Constraint Violation

A CHECK constraint fails when we try to insert data that doesn’t meet the specified condition.
For instance, trying to enter a salary below 10,000:

INSERT INTO employees (employee_id, salary) VALUES (1, 5000);

This fails because of the CHECK constraint.

 

Solution: Always verify that the data meets the CHECK conditions before inserting it.

Conclusion

Using constraints in SQL is key to building a reliable and efficient database.

 

They enforce the rule automatically, and then the data can be precise, consistent, and of sound integrity.

 

Some of the examples of these constraints would include preventing null values through NOT NULL, ensuring that only unique entries are entered, or even controlling relationships with FOREIGN KEY. Such constraints help avoid data errors while making the database more efficient and easier to query and manage.

 

Constraints in SQL act like guards: they ensure your data is clean and correct, thereby minimising the chances of error and making your systems run overall better. Adding constraints to your database design is a prudent decision to keep your environment healthy and robust.

FAQs
Yes, we can add constraints after a table is created using the ALTER TABLE command.
SQL will raise an error in this case. It prevents the addition of the same value twice, aiding in keeping your data unique and tidy.
We can check which constraints are applied by using the SHOW CREATE TABLE statement in MySQL or by referring to system tables in other databases.
Yes, some databases permit us to temporarily disable constraints, but it is not strictly recommended. For the most part, such disabling will give inconsistent data. When we want to disable a constraint, we need a great reason to do so.
Yes, we can apply more than one constraint to a column. We can use NOT NULL and UNIQUE together, for example.
brochureimg

The DevOps Playbook

Simplify deployment with Docker containers.

Streamline development with modern practices.

Enhance efficiency with automated workflows.

left dot patternright dot pattern

Programs tailored for your success

Popular

Management

Data Science

Finance

Technology

Future Tech

Upskill with expert articles

View all
Hero Vired logo
Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Technology

Certificate Program in Full Stack Development with Specialization for Web and Mobile

Certificate Program in DevOps and Cloud Engineering

Certificate Program in Application Development

Certificate Program in Cybersecurity Essentials & Risk Assessment

Finance

Integrated Program in Finance and Financial Technologies

Certificate Program in Financial Analysis, Valuation and Risk Management

Management

Certificate Program in Strategic Management and Business Essentials

Executive Program in Product Management

Certificate Program in Product Management

Certificate Program in Technology-enabled Sales

Future Tech

Certificate Program in Gaming & Esports

Certificate Program in Extended Reality (VR+AR)

Professional Diploma in UX Design

Blogs
Reviews
Events
In the News
About Us
Contact us
Learning Hub
18003093939     ·     hello@herovired.com     ·    Whatsapp
Privacy policy and Terms of use

© 2024 Hero Vired. All rights reserved