Check Constraint

Check Constraint

CREATE TABLE EMP_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.