Constraints :
Constraint is a mechanism which is used to restrict the invalid data which is entered by the end user by implementing business rules.We can apply the constraints(User defined) in two situations.
- During the creation of the table
- After the creation of the table
NOTE : System defined constraint names are always in the format of SYS_CN. Here N represents any number.
- We can apply the constraints in two levels.
1)Column Level : Applying constraints after defining the column immediately then those constraints can be called as column level constraints.
2)Table Level : Applying constraints after defining all the columns then those constraints can be called as Table level constraints.
1)UNIQUE :
=========
This constraint doesn’t allows us to enter duplicate values in a particular column in a table. We can apply the unique constraint on more than one column in the table.The main disadvantage of unique constraint is it allows NULL values.
Syntax: create table <table_name>
NOTE : Once we are violating the unique constraint properties then it automatically displays the following error message.
Ora_0001 : unique constraint (JAVANIB.sys_c5001) violated.
===========
This constraint doesn’t allows us to enter null values in a particular column in a table. We can apply the unique constraint on more than one column in the table.The main disadvantage of not null constraint is it allows duplicate values.
Syntax: create table <table_name>
Example: create table Employee(eid number(5),ename varchar2(20) not null);
NOTE : Once we are violating the not null constraint properties then it automatically displays the following error message.
Ora_01400 : can not insert null into("JAVANIB"."EMPLOYEE"."ENAME"
=========
This constraint allows us to enter the values which are satisfying the given condition. We can apply on more than one column.Check constraint allows us to enter null values.
Syntax: create table <table_name>
Example: create table Employee(eid number(5),ename varchar2(20),sal number(5) check(sal>5000));
NOTE : Once we are violating the check constraint properties then it automatically displays the following error message.
Ora_02290 : check constraint(JAVANIB
- Applying check constraint on character column.
===============
- It is a combination of unique and not null constraints. So, it doesn’t allows us to enter both null values and duplicate values.
- We can apply the primary key constraint on key attribute column in the table.
- A table can have only one primary key constraint it means that the keyword primary key should not repeat more than one time in a single table.
Note: If we try to provide more than one primary key then we get
=========
It gives default value to perticular column in the table.
Example: create table Employee(eid number(5) default 007,ename varchar2(20) );
=============
Using this constraint, we can maintain the relationship between the tables with in the database.A foreign key constraint requires values in one table to match values in another table.This constraint can also be called as referential integrity. Using this constraint we can stop the deletion operation if there are any dependencies on the parent values.
Example:
==============
Using this command we can delete the parent value forcibly even though there are some dependencies.
Example: create table child(eid number(5) primary key,
Syntax to apply Constraint after creation of table :
--------------------------------------------------------
Syntax: alter table <table_name>
List Of Constraints :
===============
- To create user defined constraint
- Syntax to enable/disable the constraint
COMPOSITE Primary Key :
======================
The primary key constraint properties are shared by more than one column with in the table at the same time.
Example: create table Product(pid number(5)
![]() | ![]() | ![]() |
No comments:
Post a Comment