Tutorials Hut

  • SQL Testing




  • SQL Constraints | Types and Examples

    SQL constraints are the rules which is applied to the data in tables to ensure data integrity and consistency. They provide a way to define and enforce rules for the data stored in the database, ensuring that only valid data is entered and preventing data inconsistencies and errors. Constraints are an important aspect of database design and development, and are used to enforce business rules and ensure data accuracy.

    In this article, we will discuss some of the most common SQL constraints and how they can be used to enforce data integrity in a database.

      • When the SQL constraints are not met by a data then INSERT/UPDATE/ DELETE etc. will be aborted.

    Below are few SQL constraints:

          1. Not Null Constraint
          2. Unique Constraint
          3. Primary Key Constraint
          4. Foreign Key Constraint
          5. Check Constraint
    SQL constraints

    1) NOT NULL Constraint

        • It ensures column cannot store NULL value
        • If column is specified as NOT NULL then ,null value can not be stored in this particular column .
        • When this constraint is applied to a column, it must contain a value for every row in the table.

    Example – 

    CREATE TABLE Employee(
    EmployeeID                  Number(6)     NOT NULL,
    Employee_FirstName        VarChar2(30)  NOT NULL,
    Employee_LastName         VarChar2(20),
    )

    2) UNIQUE Constraint

        • Make sure that all the values in a column are unique
        • UNIQUE constraint can be used either on multiple columns or on a single column with
        • PRIMARY KEY constraint automatically has a UNIQUE constraint, to guarantee the uniqueness of a column.
        • A table can have many UNIQUE constraints but can have a single primary key constraint.

    Example:

    CREATE TABLE Employee(
    EmployeeID                 Number(6) NOT NULL UNIQUE,
    Employee_FirstName         VarChar2(30)  NOT NULL,
    Employee_LastName          VarChar2(20)
    );

    3) PRIMARY KEY Constraint

        • A combination of a NOT NULL and UNIQUE.
        • No two rows in a table can have the same primary key value.
        • NULL value cannot be entered in a primary key column.

    Example:

    CREATE TABLE Employee(
    EmployeeID                      Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
    Employee_FirstName         VarChar2(30)  NOT NULL,
    Employee_LastName          VarChar2(20),
    )

    4) FOREIGN KEY Constraint

        • Ensure the referential integrity of the data in one table to match values in another table
        • Creates a kind of link between the tables.
        • It is also called as REFERENCE KEY.
        • A table may have more than one FOREIGN KEY.
        • FOREIGN KEY should not contain NULL values.

    The following statement establishes a foreign key on the Dept_id column of the Employee table that references the Dept_id column of the Departments table.

    CREATE TABLE Employee (
    EmployeeID                Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
    Employee_FirstName        VarChar2(30)  NOT NULL,
    Employee_LastName         VarChar2(20),
    Dept_id                   INT       CONSTRAINT Fkey1 REFERENCES Departments(dept_id)
    );

    5) CHECK  Constraint

        • Ensures that the value in a column meets a specific condition
        • If the value does not match the condition, then it will not be inserted into a table

    Check constraint on create table

    Write a query to create a table Employee, with columns EmployeeID, Employee_FirstName, Employee_LastName and City and The city must be Bangalore.

    CREATE TABLE Employee ( 
    EmployeeID              Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
    Employee_FirstName      VarChar2(30)  NOT NULL,
    Employee_LastName       VarChar2(20), 
    City                    VarChar2(255)   CHECK (City==’Bangalore’) );

    CHECK constraint on multiple columns

    Write a query to create a table Employee, with columns EmployeeID, Employee_FirstName, Employee_LastName and City .The City must be Mumbai, and the age of Employee  >20.

    CREATE TABLE Employee ( 
    EmployeeID                Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
    Employee_FirstName        VarChar2(30)  NOT NULL,
    Employee_LastName         VarChar2(20), 
    Age                       int, 
    City                      VarChar2(255)    CONSTRAINT chk  CHECK (City==’Mumbai’ AND Age>20));

    SQL constraints are a crucial aspect of database design and development. They provide a way to enforce data integrity and consistency, and ensure that data is entered correctly and consistently across all applications. By using constraints effectively, developers can prevent errors and inconsistencies, and create robust and reliable databases that are essential for modern applications.

    Recommended Articles:



  • SQL Testing














  • Leave a Reply

    Your email address will not be published. Required fields are marked *