Usage of check constraints - A good example

In this blog I will illustrate usage of CHECK constraint. A CHECK constraint is used to check for the right value before a value is inserted into a table.

In our situation, we have a table where a candidate's information is stored. We are going to validate the value for Children based on the Marital status using a CHECK constraint. The logic of the same is like if the candidate is married the children field can have value of 0 or more. If he is single, the field will only accept value of 0.

We will create the table Personal_Details as follows:

CREATE TABLE PERSONAL_DETAILS(
NAME VARCHAR2(20),
MARITAL CHAR(1) CHECK(MARITAL='S' OR MARITAL = 'M'),
CHILDREN NUMBER,
CHECK((MARITAL = 'S' AND CHILDREN=0) OR
(MARITAL = 'M' AND CHILDREN >=0)));

This constraint can only be kept at table level because it accesses more than one field. Now the valid values for CHILDREN field is based on MARITAL field. MARITAL is also one of 'S' or 'M'. If the field MARITAL is 'S' it will only allow 0 as a valid value. If the MARITAL field is 'M' it will allow either 0 or a value greater than 0.