Understanding Constraints on Tables in DBMS: Ensuring Data Integrity and Accuracy

Structured Query Language (SQL) is a widely used programming language for managing relational databases. It offers developers and database administrators a range of functionality and flexibility to store, manipulate, and retrieve data. However, with great power comes great responsibility, and that responsibility includes understanding the various constraints in SQL. In this blog, we will discuss the different types of constraints in SQL.

Table of Contents

1. What are Constraints in DBMS?

2. Why are Constraints Important in DBMS?

3. Types of Constraints in DBMS

4. Benefits of Constraints in DBMS

5. Conclusion

What Are Constraints in DBMS?

Constraints are restrictions or rules applied to the data in a database to maintain accuracy and reliability. These Rules or conditions are applied to database data to ensure data integrity, consistency, and adherence to business rules. Constraints prevent the entry of invalid or inconsistent data .Constraints can be applied at the column level or the table level in relational databases.

Why Are Constraints Important?

1. Data Integrity

Constraints ensure the integrity of the data in a database by enforcing rules on what can and cannot be inserted into the database. For instance, ensuring that employee IDs are unique prevents duplication and ambiguity.

2. Data Accuracy

Constraints ensure that only valid data is entered. For example, a CHECK constraint can enforce that only realistic ages are entered for customers or employees (e.g., CHECK (Age BETWEEN 18 AND 65)).

3. Referential Integrity

With FOREIGN KEY constraints, relationships between tables are safeguarded. This ensures that child records (e.g., orders, employees) always relate to valid parent records (e.g., customers, departments), maintaining data consistency across tables.

4. Enforcing Business Rules

Constraints help ensure that the data adheres to business logic. For example, a business rule may require that no item in the Products table can have a negative price, which can be enforced by a CHECK constraint.

Types Of Constraints In DBMS

We have 6 types of key constraints in DBMS

NOT NULL: ensures that the specified column doesn’t contain a NULL value.

UNIQUE : provides a unique/distinct values to specified columns.

DEFAULT: provides a default value to a column if none is specified.

CHECK :checks for the predefined conditions before inserting the data inside the table.

PRIMARY KEY: it uniquely identifies a row in a table.

FOREIGN KEY: ensures referential integrity of the relationship

1. 1. Not Null Constraints

   The NOT NULL constraint is a type of domain constraint in DBMS. It specifies that a column cannot have a null value. This means that every row in the table must have a value for that column. The NOT NULL constraint is used to ensure that the data in the database is complete and accurate.

Example

CREATE TABLE customers (

ID_No int Not Null ,

Name varchar(50),

Age int ,

Phone int primary key

);

• In the above example, we have applied not null on columns ID which means whenever a record is entered using insert statement Id_No columns should contain a value other than null.

• Consider ,we have two other columns address and salary, where not null is not applied which means that you can leave the row as empty or use null value while inserting the record into the table.

2.Unique Key constraint

• Sometimes we need to maintain only unique data in the column of a database table, this is possible by using a unique constraint

• Unique constraint ensures that all values in a column are unique

CREATE TABLE Persons (

ID_No int UNIQUE,

Name varchar(255) NOT NULL,

Age int,

Phone int

);

• In the above example, as we have used unique constraint on ID column we are not supposed to enter the data that is already present, simply no two ID values are same.

  3.Check constraint :

  The CHECK constraint is a type of domain constraint in DBMS. It specifies a condition that all values in a column must satisfy. The CHECK constraint is used to ensure that the data in the database is valid and consistent.

CREATE TABLE STUDENT (

ID_No int primary key ,

Name varchar(255) ,

Age int, CHECK (Age>=18)

);

• As we have used a check constraint as (Age>=18) which means values entered by the user for this age column while inserting the data must be greater than or equal to 18 otherwise an error is shown .

  4.Primary Key Constraint:

• A primary key is a column or a set of columns that uniquely identifies each row in a table.

• The primary key constraint ensures that the values in the specified columns are unique and not NULL.

• There can be only one primary key in a table.

  

  CREATE TABLE Student(

  Roll No INT PRIMARY KEY,

  NAME VARCHAR (20) ,

  AGE INT ,

  Gpa DECIMAL(4, 2)

  );

  • Here we have used the primary key on ID column then ID column must contain unique values i.e one ID cannot be used for another student.

  • If you try to enter duplicate value while inserting in the row you are displayed with an error.

  • Hence primary key will restrict you to maintain unique values and not null values in that particular column.

5.Foreign Key Constraint:

• A foreign key is a column or a set of columns in a table that refers to the primary key of another table.

• It establishes a relationship between the two tables, enforcing referential integrity.

• The foreign key constraint ensures that values in the foreign key column(s) match values in the referenced primary key column(s).

• The foreign key constraint ensures referential integrity, meaning that relationships between tables are maintained, and it helps prevent inconsistencies in the data. It’s a powerful tool for enforcing relationships between tables in a relational database.

Referenced Table

CREATE TABLE Student_Details(

Roll_No INT not null ,

NAME VARCHAR (20) ,

Course_Id int primary key

);

Referencing Table

CREATE TABLE Student_Marks(

Course_ID INT ,

Gpa Decimal(3,2),

REFERENCES Student_Details(Course_Id)

);

Key Concepts:

1. Referenced Table (Student_Details):

• This table acts as the parent table.

• The Course_Id is the primary key, meaning that each course will have a unique identifier.

2. Referencing Table (Student_Marks):

• This table acts as the child table.

• The Course_ID field references the Course_Id in the Student_Details table, creating a foreign key relationship between the two tables.

6.DEFAULT  Constraint

• Default clause in SQL is used to add default data to the columns

• When a column is specified as default with some value then all the rows will use the same value i.e each and every time while entering the data we need not enter that value

• But default column value can be customized i.e it can be overridden when inserting a data for that row based on the requirement.

CREATE TABLE emp (

ID_No int Primary key ,

Name varchar(255) NOT NULL,

company varchar(50) Default 'PrepInsta',

Phone int

);

• As a result, whenever you insert a new row each time you need not enter a value for this default column that is entering a column value for a default column is optional and if you don’t enter ,the same value is considered that is used in the default clause.

Common Errors Due to Constraints

When working with constraints, some common errors may occur if data violates the rules set by the constraints:

• NOT NULL Violation: Trying to insert NULL values into a column that has a NOT NULL constraint.

• UNIQUE Violation: Inserting duplicate values in a column that requires uniqueness.

• FOREIGN KEY Violation: Attempting to insert a value in a foreign key column that does not exist in the referenced table.

• CHECK Constraint Failure: Entering a value that does not satisfy the condition imposed by the CHECK constraint.

To avoid these errors, it is crucial to understand the constraints applied to the database schema and ensure that input data adheres to the defined rules.

Benefits of Constraints in DBMS Tables

1. Data Integrity: Constraints ensure that the data entered into the database is accurate and consistent, maintaining the integrity of the database.

2. Prevention of Invalid Data: They restrict the types of data that can be inserted, preventing invalid entries that could compromise the database's reliability.

3. Referential Integrity: Foreign key constraints enforce relationships between tables, ensuring that references to data in one table remain valid in related tables.

4. Uniqueness: Unique constraints prevent duplicate entries in specified columns, ensuring that each entry is distinct.

5. Default Values: Default constraints automatically assign values to columns when none are provided, ensuring that all records have complete data.

6. Custom Validation: Check constraints allow the definition of custom rules for data entry, further refining the acceptable data range.

7. Performance Optimization: By defining constraints, the DBMS can optimize queries more effectively based on the data structure, potentially improving performance.

8. Simplified Data Maintenance: Constraints reduce the need for extensive application-level validation, making it easier to maintain data quality.

9. User-Friendly Data Entry: Constraints can simplify the process for users entering data, minimizing the likelihood of errors.

Conclusion

Constraints in DBMS are essential to maintaining the integrity, accuracy, and consistency of the data in a database. By using constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT, developers can enforce business rules directly at the database level, ensuring high-quality data.

As databases continue to grow in complexity and scale, understanding and applying constraints properly is critical to building robust, reliable systems. Whether you're managing small-scale applications or large-scale enterprise systems, constraints are a fundamental feature of DBMS that ensures the smooth functioning of data management processes. Proficiency in comprehending various constraint types is indispensable for crafting resilient databases and upholding data quality standards.

Happy Learning !!!

Regards!
Together, we can grow and learn.
Please share this again with your network.