Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Use of constraints in SQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.11k
    Comment on it

    Constraints in SQL are used to define rules for the  table and columns in a database and also make sure that data are accurate and trusted in the database. Constraints are responsible for the termination of action if any violation is found.

    Structured Query Language is used to manage the stored data of RDBMS.

    We can define a constrains for existing table by ALTER TABLE or at time of new table creation.


    Constraints are two types:

    Column level: This type of constrains are applied only on a column

    Table level: This type of constrains are applied only on whole table

     

    Constraints are used to make sure that the unity of data is managed in the database. The most commonly used SQL constraints are described as below...

    1. PRIMARY KEY
    2. FOREIGN KEY
    3. NOT NULL
    4. UNIQUE   
    5. CHECK
    6. DEFAULT

     

    PRIMARY KEY constraint: A Primary key is a type of restriction which uniquely define each record in a database. It is a combination of a UNIQUE & NOT NULL constraint.  It also helps to find a particular record from database thats why we make primary key as index.

     

    #PRIMARY KEY constraint at Table Level
    CREATE table users (uid int PRIMARY KEY, fist_name varchar(255) NOT NULL, mobile int);
    
    #PRIMARY KEY constraint at Column Level
    ALTER table users add PRIMARY KEY (uid);

     

    FOREIGN KEY constraint:  A Foreign key is basically used to create a relation between two tables.It is also used to restrict actions that would delete relation between tables.

     

    #FOREIGN KEY constraint at Table Level
    
    CREATE table user_detail(user_id int PRIMARY KEY, user_city varchar(255) NOT NULL, uid int FOREIGN KEY REFERENCES user(uid));
    
    
    #FOREIGN KEY constraint at Column Level
    
    ALTER table user_detail add FOREIGN KEY (uid) REFERENCES user_detail(uid);

     

    NOT NULL constraint: NOT NULL constraint is a type of restriction which restricts a field to having a NULL value. It means NOT NULL  field can not accept a null type value. NOT NULL constraint only defines at column level not a table level constraint.

     

    #NOT NULL constraint
    
    CREATE table user(uid int NOT NULL, first_name varchar(255), mobile int);

     

    UNIQUE Constraint: An UNIQUE constraint is a type of restriction which restricts a field that will have only unique values that means  UNIQUE constraint field will never accept duplicate data. It can be defined at column level or table level.

     

    #UNIQUE constraint on table creation
    
    CREATE table user(uid int NOT NULL UNIQUE, first_name varchar(255), mobile int);
    
    
    #UNIQUE constraint at Column Level
    
    ALTER table user add UNIQUE(uid);

     

    CHECK constraint: CHECK constraint is used to apply a condition on field and it  works like a condition which applied before saving data into field of table.

     

    #CHECK constraint at Table Level
    
    create table user(uid int NOT NULL CHECK(uid > 0), first_name varchar(255) NOT NULL, mobile int);
    
    
    
    #CHECK constraint at Column Level
    
    ALTER table user add CHECK(uid > 0);

     

    DEFAULT constraint: It is used to set default value for all new records of a column into table, if no any value is passed.

     

    CREATE table users (uid int PRIMARY KEY, fist_name varchar(255) NOT NULL, mobile int(11) DEFAULT '0000000000' );

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: