×
>
<

Aptitude

Check in DBMS

Check

  • Suppose in real-time if you want to give access to an application only if the age entered by the user is greater than 18 this is done at the back-end by using a check constraint
  • Check constraint ensures that the data entered by the user for that column is within the range of values or possible values specified.

Syntax for check constraint

Syntax

  
CREATE TABLE STUDENT (
     column1 datatype(size),
     column2 datatype(size),
     column3 datatype(size),
    CHECK (condition)
);  
  
Example
  
CREATE TABLE STUDENT (
    ID int ,
    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 less than or equal to 18 otherwise an error is shown 
  • Simply, the only possible values that the age column will accept is [0 -17].

Check constraint at table level
Example
  
CREATE TABLE student {
   ID int ,
   Name varchar(255) ,
   Program interface specificationAge int,
   City varchar(255),
   CONSTRAINT CHK_Person CHECK (Age>=18 AND City='hyderabad')
); 
  

  • You can apply check constraint to more than one column at a time 
  • In the above example we have used two conditions with the check constraint Age>=18 AND City=’ Hyderabad’ , as a result, both the columns age and city will be checked while data is entered  i.e the only possible values that are accepted for the column age are 0 -17 and city must be only Hyderabad

Primary Key VS Unique Key
Primary Key

  • Identifies  a record uniquely in a table 
  • It will not allow null values 
  • A clustered index is created
  • Only one primary key is allowed in a table

Unique Key

  • It helps to maintain unique data in the required columns of the table 
  • It will allow null values 
  • Non clustered indexes are created 
  • A unique key can be applied to any number of columns in the table