Constraints:
   Constraints are used to avoid the entry of invalid data into the tables.
   Constraints are applied to the columns


Types of Constaints:
1. UNIQUE
2. NOT NULL
3. CHECK
4. PRIMARY KEY
5. FOREIGN KEY

----------------------------------------------------------------
Type_Code		Type_Description			Acts_On_Level
  C				Check on a table			Column
  O				Read Only on a view			Object
  P				Primary Key					Object
  R				Referential AKA Foreign Key	Column
  U				Unique Key					Column
  V				Check Option on a view		Object
----------------------------------------------------------------

1. UNIQUE
 - Unique column will not accept duplicate records
 - Unique column can be left blank (can insert null value)
 - Unique should not be applied on LONG & LONG RAW datatype columns.
 - Unique can be applied in both CREATE table & ALTER table statements.


UNIQUE in Create Table statement:
syntax:
create table <tableName>(<col1> <datatype> <size> constraint <constraintName> UNIQUE);


Q: How to check all the table constraints?
Ans: We need to query the USER_CONSTRAINTS table.

select constraint_Name, constraint_type from user_constraints where table_name = <tableName>;


Q: How to disable, Enable & drop the UNIQUE constraints?
Ans:
alter table <tableName> enable constraint <constraintName>;
alter table <tableName> disable constraint <constraintName>;
alter table <tableName> drop constraint <constraintName>;


UNIQUE in alter Table statement:
syntax:
alter table <tableName> add constraint <constraintName> UNIQUE <colName>;
--------------------------------------------

2. NOT NULL:
 - The not null column cannot be left blank
 - It can accept duplicate records.
 - It can be applied both in create table & alter table statement

Note: Applying NOT NULL makes it a mandatory field. Hence we have to be careful before applying the NOT NULL constraint to the column. If it requires as per the business then only we will apply Not null constraint to the columns.


NOT NULL in create table statement:
syntax:
create table <tableName> (<colName> <datatype> <size> constraint <constraintName> NOT NULL);


Q: How to disable, Enable & drop the NOT NULL constraints?
Ans:
alter table <tableName> enable constraint <constraintName>;
alter table <tableName> disable constraint <constraintName>;
alter table <tableName> drop constraint <constraintName>;


NOT NULL in alter table statement:
syntax:
alter table <tableName> MODIFY (<colName> <datatype> <size> constraint <constraintName> NOT NULL);
----------------------------------------

3. CHECK:
   -This constraint is used on the column to apply the business logic. This is the only constraint which accepts logical operators.
   -It can be applied both in create table & alter table statement.

CHECK in create table:
syntax:
create table <tableName>(<colName> <datatype> <size> constraint <constraintName> CHECK <condition>);



Q: How to disable, Enable & drop the CHECK constraints?
Ans:
alter table <tableName> enable constraint <constraintName>;
alter table <tableName> disable constraint <constraintName>;
alter table <tableName> drop constraint <constraintName>;


CHECK in alter table statement:
syntax:
alter table <tableName> add constraint <constraintName> CHECK <condition>;

Ex:
alter table SAMPLE add constraint chk1 CHECK (age between 18 and 100);
alter table SAMPLE add constraint chk1 CHECK (age>=18 and age<=100);
---------------------------------------

4. PRIMARY KEY (PK)
 - It is mainly used to achieve data uniqueness.
 - The PK column cannot accept duplicate records
 - The PK column cannot be left blank
 - The PK constraint cannot be applied on LONG OR LONGRAW datatype columns
 - We can apply 1 PK per table OR more than 1 PK (Composite Primary Key) per table.
 - Automatically index will be created for PK column
 - PK can be applied both in create table & alter table statements.
 - PK column is also used to establish the Parent & child table relationship with child table Foreign Key column.

Note: PK is a combination of UNIQUE & NOT NULL


PRIMARY KEY in create table:
syntax:
create table <tableName>(<colName> <datatype> <size> constraint <constraintName> PRIMARY KEY);



Q: How to disable, Enable & drop the PRIMARY KEY constraints?
Ans:
alter table <tableName> enable constraint <constraintName>;
alter table <tableName> disable constraint <constraintName>;
alter table <tableName> drop constraint <constraintName>;


PRIMARY KEY in alter table statement:
syntax:
alter table <tableName> add constraint <constraintName> PRIMARY KEY <colName>;

Q: How to apply >1 PK per table (Composite PK)
Ans:
alter table <tableName> add constraint <constraintName>
PRIMARY KEY (<colName1>, <colName2>);

-----------------------------------

5. FOREIGN KEY (FK):
 - FK constraint must be applied in the Child table
 - FK column can accept duplicate records
 - FK column can accept blank values
 - FK column in the Child table should refer to PK column in the Parent table to establish child & parent table relationship.
 - To creat parent & child table relationship, both the tables must have a common column.
 - The table with PK constraint will be a Parent table & the table with FK constraint will act as a child table.
 - The FK column will accept only those records which are present in the PK column of the Parent table.
 - We cannot delete the Parent records directly If it has a corresponding child records.
 - FK can be applied both in create table & alter table statements.


FOREIGN KEY in create table:
syntax:
create table <tableName>(<colName> <datatype> <size>, constraint <constraintName> FOREIGN KEY (<child table colName>) references <parentTableName> (<Parent table PK colName>));

Ex: Create 2 tables
 1. Student table with PK constraints on ID column
 2. Library table with FK constraints on ID column

Q: How to disable, Enable & drop the FOREIGN KEY constraints?
Ans:
alter table <tableName> enable constraint <constraintName>;
alter table <tableName> disable constraint <constraintName>;
alter table <tableName> drop constraint <constraintName>;


FOREIGN KEY in alter table:
syntax:
alter table <tableName> add constraint <constraintName> FOREIGN KEY (<child table colName>) references <parentTableName> (<Parent table PK colName>);

----------------------------------
ON DELETE CASCADE: this is applied along with FK constraint. So that we can delete the parent records directly which is having corresponding child records.
   By doing this the child record is also deleted automatically along with parent records.


ON DELETE SET NULL: this is applied along with FK constraint. So that we can delete the parent records directly which is having corresponding child records.
   By doing this the child record will not be deleted. But only the reference is deleted.

