Tutorials Hut

Tutorials Hut




  • SQL Constraints | Types and Examples

    SQL constraints are the rules which is applied to the data in tables.

      • 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 .

    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));
    Recommended Articles:

















  • Leave a Reply

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

    Scroll to Top