Business Rule Constraint

Constraints are the conditions which must be satisfied by the data to store it into record. Constraints are checks which are applied on the table to a specific column or more then one column. If the data satisfy all these conditions then it is valid unless an error is raised. Constraints are mainly of two types:

· Input output constraint

· Business rule constraint

Here you will know about business rule constraint. These rules are applied to data prior to the data is being inserted. Business rule constraints are the constraints like salary should be less then 30000. These types of rules are applied by two ways:

· Null value

· Check

Null value constraint

There may be some records which don't have value. This is possible because the value of that field don't exist. If the column is set as "null" value then oracle will place a null value when the value is not assigned to the record.

"Null" value is different from the zero and a blank. A null value can be inserted into column of any data type. Features of null value:

· A null value is applied when the value related to that field is not available.

· Null value is not equal to zero when it is inserted into the number data type and not equal to the space when inserted into character data type.

· A null value is always evaluated as null.

· Null value can be inserted into any data type.

· If a column is assigned a null value, oracle engine ignore all the other constraints applied to it.

Syntax of defining the not null constraint at column level:

() not null

Example:

Create table employee (eid varchar2 (4), name varcher2 (30), dob date not null);

This query will create a table with date of birth as not null. Now for each employee you have to enter the date of birth otherwise an error is raised.

The not null constraint can be applied only at column level. This constraint can't be applied to table level.

Check constraint

Business rules can be applied to the table by the "check" constraint. Check constraint includes a logical expression and evaluated when you insert a value. The expression is evaluated as true or false.

Syntax of check constraint at column level:

() check ()

Example:

Create table employee (eid varchar2 (4), name varchar2 (30), salary number (5) check (salaryNow if you try to insert the data where salary is greater then 30000 an error is raised. The data which you want to insert must satisfy the constraint that salary must be less then 3000. If the expression is evaluated to true then the data can be inserted.

Syntax of check constraint at table level:

Check ()

Example:

Create table employee (eid varchar2 (4), name varchar2 (30), salary number (5), check (salaryIt will also behave same as above defined syntax at column level.

But there are some restrictions on the check constraint:

· The expression must be a Boolean expression and is evaluated by the data within the row.

· The logical expression should not have a sub query.

· The condition doesn't include the sysdate, uid, user.