Adding Domain Integrity Constraints
The user has to necessarily give values for the columns containing NOT NULL values. The NOT NULL constraint is ideal in cases where the value for the column must exist. Let consider an organization which requires storing all the employee information. In that case, the employee name column cannot be left blank.
What is NULL?
NULL means some un-known value. NULL values can always be updated later with some values. Remember it is not equivalent to zero.
Example
CREATE TABLE employee_master (empno NUMBER, empname varchar2 (20) CONSTRAINT NN_ENAME NOT NULL);
While inserting records to this table,
INSERT INTO employee_master VALUES (1,null);
INSERT INTO employee_master VALUES (1,null)
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HEMA"."EMPLOYEE_MASTER"."EMPNAME")
In the above conditions, since empname column has a constraint, it ensures in which some values are entered and thus the error is increased.
Check Constraints are used to perform the checking of conditions based on the values entered. CHECK constraint is used to enforce business rules as shown in the following instance:
Example
CREATE TABLE employee_master (empno NUMBER, empname VARCHAR2 (20), sex CHAR (1) CONSTRAINT chk_sex CHECK (SEX IN (‘M’,’F’,’m’,’f’))
);
In the sex column, valid values are ’F’ or ‘M’,’m’ or’f’ if values entered do not match the condition error message appears on the screen. As shown as follows:
INSERT INTO employee_master VALUES (2,'Aditya','b'); INSERT INTO employee_master VALUES (2,'Aditya','b')
*
ERROR at line 1:
ORA-02290: check constraint (HEMA.CHK_SEX) violated
Another instance example for using CHECK Constraint is given below. This instance checks for constraints on salary which a company provides. The minimum salary which the organization provides is 1000 and the maximum is 7000. To check for this type of constraint use,
Example
CREATE TABLE employee_master (Empcode NUMBER, empname VARCHAR2 (20), Salary NUMBER CONSTRAINT CHK_SAL CHECK (salary BETWEEN 1000 and 7000));
In the above instance, BETWEEN operators is used to check if the salary lies within the range of 1000 to 7000.