Please tell me about constraints in oracle

What are the various types of constraints in oracle (table level and column level) and what is the difference?

ANSWER:
There are 6 different types of constraints available in Oracle 
NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY (referential) and REF. All but the NOT NULL constraint can be defined at either the table level (out of line) or the column level (in line).

NOT NULL constraints prevent column values being left undefined and so can only be defined at the column level.

Example:

CREATE TABLE customer(
customer_id NUMBER NOT NULL,
customer_name VARCHAR2(80)
customer_address VARCHAR2(2000)
)
The other constraints must be defined at the table level if they apply to more than one column but they can still be defined at the table level even if they only apply to one column (via a
CREATE TABLE or ALTER TABLE statement).

CHECK constraints can be applied to one or more columns and are used to restrict the range of allowable values for those columns.

For example suppose we define a CUSTOMER table and want to ensure that the total_orders column is always positive, we could define it as follows:

CREATE TABLE customer(
customer_id NUMBER NOT NULL,
customer_name VARCHAR2(80)
customer_address VARCHAR2(2000)
total_orders NUMBER CONSTRAINT orders_gt_zero CHECK (total_orders > 0)
)
UNIQUE and PRIMARY KEY constraints are similar in that they both enforce uniqueness (via an index) but there are some important differences to note. 


A table can have only oneprimary key - which can span as many columns as you like - and all columns in the primary key must also be NOT NULL. If every column in the table is part of the primary key then you have an index-organised table.

There can be as none, one or many different unique columns on a table each with a different index or they can all be part of the same index. Example:

CREATE TABLE customer_orders(
order_no NUMBER ,
customer_id NUMBER,
customer_ref NUMBER UNIQUE
...
CONSTRAINT cust_orders_pk PRIMARY KEY (order_no NUMBER , customer_id NUMBER) )

FOREIGN KEY (referential) constraints are used to define the relationships between tables and another and to prevent orphaned records (to ensure for example that no orders are placed for non-existent customers).

Example:

CREATE TABLE customer_orders(
order_no NUMBER ,
customer_id NUMBER CONSTRAINT fk_custno REFERENCES customer(customer_id),
customer_ref NUMBER
...
)

A FOREIGN KEY constraint must refer to a unique or primary key column in the referenced table.

REF constraints apply to REF columns which reference an object in another object type or in a relational table and enable you to define a scope constraint, a rowid constraint, or a referential integrity constraint.