SQL Check Constraint



The attribute or column in a table with CHECK Constraint can restrict the column value .



            Ex:-Lets see the declaration of CHECK constraint during creation of table .

                 





                          Create table Vehicle(
                               vehicle_id            int Primary key,
                              vehicle_name     varchar(30),
                              mileage int           int NOT NULL CHECK ( mileage>19  AND mileage <101)
                                              );

                     





  In the above defined vehicle table the column mileage is declared with NOT NULL and CHECK constraint .

  The check constraint for mileage columns restricts its value , should be in the range between 20 and 100

  Suppose if you try to insert the value which is outside the range then it database throws error message.



Inserting value:-

 Incorrect :             


Insert into Vehicle values(1,"Honda Activa",110 );




      Error : Inserted value for mileage column is out of range

.

 As you can see in the above insertion operation for Vehicle table, the mileage column is inserted
with value which is outside the range as per defined in CHECK clause, so the database throws the error message .



Correct :

  

 Insert into Vehicle values(1,"Honda Activa",52);


                 



Vehicle

vehicle_id vehicle_name mileage
1 Honda Activa 50

No comments:

Post a Comment