Constraints

When you bind a business rule to a column in the table then those rules are called the Constraints. Constraints are defined while creating the table. Say for example, you cannot have an employee who does not have a name, then employee name column in employee table should be a NOT NULL column. The NOT NULL is a constraint.

The following table shows the constraint types and short descriptions.

Examples for some of the rules usually implement through the business rules.

NOT NULL
If we have a business rule saying that all customers should have a name, we cannot have any customer with out a name. So to implement that business rule we can create customer table and specify customer name column as NOT NULL (constraint)
Example
CREATE TABLE EMPLOYEE (EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR(4) NOT NULL);

CHECK
Check constraint is used where we define a condition on a column. Check constraint consists of the keyword

col_name datatype CHECK (col_name in(value1, value2))

Example
If you have a business rule saying that all employees in the organization should get atleast $500 then we can use CHECK constraint while creating table.

CREATE TABLE EMPLOYEE ( EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR(4) NOT NULL, SALARY NUMBER(7,2) CHECK (SALARY > 500) );

DEFAULT
While inserting a row into a table without giving values for every column, SQL must insert a default value to fill in the excluded columns, or the command will be rejected. The most common default value is NULL. This can be used with columns not defined with a NOT NULL.

Default value assigned to a column while creating the table using CREATE TABLE operation.

Example
CREATE TABLE ITEM (ITEM_ID NUMBER(4) PRIMARY KEY, ITEM_NAME VARCHAR(15),
ITEM_DESC VARCHAR(100), QOH NUMBER(4) DEFAULT 100)

Assigning a default value 0 for numeric columns makes the computation.

PRIMARY KEY
Primary Key in a table is a unique identifier of a row. For example,if you are maintaning the customer profiles, you should assign particular number to each one. So customer_number should be defined as a Primary key in Customer table.

REFERENCES
is a Foreign key. A foreign key column value refers a column in another table to check whether the value exists or not.

UNIQUE
The values entered into a column are unique ie no duplicate values exists.This constraint ensures business that there is no duplicates allowed.