Constraints;
1.Unique
2.Not Null
3.check
4.primary key
5.foreign key


Table level : Constraints are set of rules or bussiness rules enforced on data to restrict the users in order to insert
duplicates and null values.

1.Unique constraints;
If we impose a unique constraints on a column it wont allow duplicates but it will allow null values.

2. Not null constraints :
If we impose a Not Null constraints on a column it wont null values  but it will allow Duplicates.

3. Check Constraints:

If we impose a Check constraints on a column it will check the value at the time of insertion.

4 Primary key :

It is a combination of unique constraint and Not null Constraint. If we impose a primary key it wont allow the Duplicates and Not null values

**Each table should have only one primary key, On which column we impose the primary key that column should not consist the duplicates and null values.

5.Foreign Key: It is refered as ------------------key,
if we impose a foreign key constraint on a column or table that column or table refered as another table.
--------------------------------------------------------------------------------------------------------------- Imposing the constraints while creating the table
---------------------------------------------------------------------------------------------------------------
Syntax for table level Constraints
In table level we cannot impose the not null constraint
create table <table name>(column1 datatype(size),column2 datatype(size),constrainttype(column1),constrainttype(column2);

eg:
select username,account_status from dba_users;

create table biomorf(sno number(10),name char(20),department varchar2(10),salary number(10),
unique(sno),primary key(salary));

insert into biomorf values(&sno,'&name','&department',&salary);

create table ramu1(sno number(8),name varchar(10), salary number(10),primary key(sno),unique(name));
desc USER_CONSTRAINTS
select * from user_constraints;   ---- data dictionary table for constraints
select * from user_cons_columns;  ---- data dictionary table for constraints with columnname
------------------------------------------------------------------------------------------------------------------------
to create a table whith constraint name
In table level we cannot impose the not null constraint
Syntax:
create table <tablename>(column1 datatype(size),column2 datatype(size)...,constraint <constraint name> constrainttype(column name);
select * from tab;

CREATE table infronics(sno number(10),name char(10),mobile number(10),constraint p_k primary key(sno),constraint u_k unique(name));
select * from user_constraints;   ---- data dictionary table for constraints
select * from user_cons_columns;  ---- data dictionary table for constraints with columnname

------------------------------------------------------------------------------------------------------------------------ 
Imposing the constraints to columns while creating the table
------------------------------------------------------------------------------------------------------------------------
Syntax for Column level Constraints
Only in column level we can impose the not null constraint
create table <table name>(column1 datatype(size) constraint_type,
column2 datatype(size) constraint_type,.....);

manual imposing constraint name:
create table <table name>(column1 datatype(size) constraint Constraint_name constraint_type,
column2 datatype(size)constraint Constraint_name constraint_type,.....);

Eg:
select * from tab;
create table info2(sno number(2) unique,name char(10) primary key,salary number(10) not null);
create table info31(sno number(2) constraint l_1 unique,
name char(10) constraint l_b primary key,
salary number(10)constraint l_c not null);
select * from user_constraints;   ---- data dictionary table for constraints
select * from user_cons_columns;  ---- data dictionary table for constraints with columnname

------------------------------------------------------------------------------------------------------------------------ Imposing the constraints for existing columns
------------------------------------------------------------------------------------------------------------------------
NOT Null Constraint: only for not null constraint will use the modify
Syntax:
>Alter table <table name> modify <column name> constraint <constraint name> <constraint type> ;
Eg:
alter table biomorf1 modify sno constraint l_l not null;

drop the constraint for particular column:
Syntax:
alter table <table name>drop constraint <constraint name>
Eg:
alter table biomorf1 drop constraint p_p;
---------------------------------------------------------------------------------------------------------
Unique constraint :

syntax :
>Alter table <table name>add constraint <constraint name> <constraint type>

Eg:alter table biomorf add constraint p_p unique(name);

drop the constraint for particular column:
Syntax:
alter table <table name>drop constraint <constraint name>
Eg:
alter table biomorf1 drop constraint p_p;
------------------------------------------------------------------------------------------------------------------------
check constraint:

Syntax:
>Alter table <table name> add constraint <constraint name> <constraint type>

Eg:
>Alter table test add constraints c_k1 check(salary>800>
------------------------------------------------------------------------------------------------------------------------
primary key and foreign key constraints;

Primary key :
syntax
alter table <table name> add constraint <constraint name> constraint type(column name)
Eg:
alter table biomorf add constraint p_k primary key(SALARY);
Foreign Key :
Syntax:
alter table <table name> add constraint <constraint name> constraint type(column name) references tablename<column name);
Eg:
alter table infronics add constraint f_k foreign key(salary) references biomorf(salary);

NOTE:
While inserting the data the child table will depends on the parent table
while deleting the data in parents table it will depends on the child table data

Drop constraints :
alter drop <tablename> drop constraint <constraint name>;
Eg:
alter table biomorf drop constraint p_k;
alter table infronics drop constraint f_k;
------------------------------------------------------------------------------------------------------------------------
Rename constraints :
Syntax :
alter table <table name> rename constraint <old constraint name > to < new constraint name>;
Eg:
alter table biomorf rename constraint p_k to f_k;
------------------------------------------------------------------------------------------------------------------------
ON Delete Cascade :
If we are imposing the foreign key with option on delete cascade . if we delete a record from the parent table , automatically all the rows will delete from
the child table related to that record
syntax :
alter table <table name> add constraint <constraint name> <table name(column name) on delete cascade;
Eg :
alter table infronics add constraint f_k foreign key(salary) references biomorf(salary) on delete cascade;
------------------------------------------------------------------------------------------------------------------------
ON delete Set Null :
If we are imposing the foreign key with option on delete set null . if we delete a record from the parent table , records from the child table on which ever column we impose a foreign key on that column automatically null will set
Syntax:
alter table <table name> add constraint <constraint name> <table name(column name) on delete set null;
Eg:
alter table infronics add constraint f_k foreign key(salary) references biomorf(salary) on delete set null;
------------------------------------------------------------------------------------------------------------------------