What are Constraints in SQL?

In SQL, constraints are rules applied to the columns of a table to enforce data integrity and ensure the accuracy and reliability of the data within the database. There are several types of constraints, each serving a specific purpose:

1. Primary Key Constraint: This constraint uniquely identifies each record or row in a table. It ensures that the column will not have null values and that each value is unique.

    CREATE TABLE Employees (
        EmployeeID INT NOT NULL,
        LastName VARCHAR(255) NOT NULL,
        FirstName VARCHAR(255),
        PRIMARY KEY (EmployeeID)
    );
   

2. Foreign Key Constraint: This constraint is used to link two tables together. It ensures that the value in a column (or group of columns) matches values in another table, thereby maintaining referential integrity.

    -- Create the parent table
    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(50) NOT NULL
    );
   
    -- Create the child table with a foreign key constraint
    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(50) NOT NULL,
        department_id INT,
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
    );
   
    -- Insert data into the parent table
    INSERT INTO departments (department_id, department_name) VALUES (1, 'HR');
    INSERT INTO departments (department_id, department_name) VALUES (2, 'Engineering');
   
    -- Insert data into the child table
    INSERT INTO employees (employee_id, employee_name, department_id) VALUES (1, 'John Doe', 1);
    INSERT INTO employees (employee_id, employee_name, department_id) VALUES (2, 'Jane Smith', 2);
   
    -- Query to show the result
    SELECT e.employee_id, e.employee_name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id;
   

3. Unique Constraint: This constraint ensures that all the values in a column are different. Unlike the primary key constraint, a table can have multiple unique constraints.

    ALTER TABLE table_name
    ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);
   

4. Not Null Constraint: This constraint ensures that a column cannot have a NULL value. It is used when a column must always have a value.

    ALTER TABLE table_name
    MODIFY column_name datatype NOT NULL;
   

5. Check Constraint: This constraint allows you to specify a condition that must be met for the data to be inserted into the table. It ensures that the value in a column meets a specific criteria*.*

    CREATE TABLE Employees (
        EmployeeID int NOT NULL,
        FirstName varchar(255) NOT NULL,
        LastName varchar(255) NOT NULL,
        Age int,
        Salary decimal(10, 2),
        CONSTRAINT chk_Age CHECK (Age >= 18 AND Age <= 65),
        CONSTRAINT chk_Salary CHECK (Salary >= 30000)
    );
   

6. Default Constraint: This constraint provides a default value for a column when no value is specified. It ensures that a field always has a value, even if the user does not enter one.

    CREATE TABLE Employees (
        EmployeeID int NOT NULL,
        FirstName varchar(255) NOT NULL,
        LastName varchar(255) NOT NULL,
        HireDate date NOT NULL,
        Salary decimal(10, 2) DEFAULT 45000.00
    );
   

These constraints are integral to maintaining the structure and integrity of your database, ensuring that the data stick to business rules and constraints set by the application.

What is Data Filtration in SQL?

Data filtration in SQL refers to the process of retrieving specific data from a database by applying certain conditions or criteria. This is typically done using theSELECTstatement combined with theWHEREclause to filter records based on specified conditions.

By filtering data, you can focus on the most relevant information and exclude unnecessary or irrelevant data from your query results.

For example, suppose you have a table named Employees and you want to retrieve the details of employees who are older than 30 years. You can use the following SQL query:

SELECT * FROM Employees
WHERE Age > 30;

In this query, the WHERE clause filters the records to include only those where the Age column has a value greater than 30. This way, you can efficiently extract and analyze the data that meets your specific needs.

Data filtration can also involve more complex conditions using logical operators like AND, OR, and NOT. For instance, if you want to find employees who are older than 30 and have a salary greater than 45,000, you can use the following query:

SELECT * FROM Employees
WHERE Age > 30 AND Salary > 45000;

Additionally, SQL provides various functions and operators to further refine your data filtration. You can use comparison operators (=, !=, <, >, <=, >=), pattern matching with LIKE, and range checks with BETWEEN. Here’s an example of using LIKE to find employees whose first name starts with 'J':

SELECT * FROM Employees
WHERE FirstName LIKE 'J%';

In summary, data filtration in SQL is a powerful technique that allows you to extract meaningful and relevant data from large datasets. By applying specific conditions, you can streamline your data analysis and ensure that you are working with the most proper information.

Thank you for reading the blog. I hope you found it informative and beneficial for your learning. Please feel free to leave a comment, like, and share it with your colleagues. Happy learning!

Blog Recap

In SQL, constraints are rules applied to table columns to enforce data integrity, including primary key, foreign key, unique, not null, check, and default constraints.

Data filtration involves retrieving specific data by applying conditions using the SELECT statement and WHERE clause, enabling focused data analysis through various operators and functions.

---

Resources

if you prefer Video format to learn these You can checkout this Youtube Video

About Me

Hey folks! I’m Aman, I am currently pursing 2nd year of BCA Information-Technology and am learning frontend Development with the help of Communities.

Am doing LearnInPublic & BuildInPublic so I’ll be sharing my learning in my Socials and I’ll make contents also by documenting my learnings.