ORACLE INTEGRITY CONSTRAINTS
Oracle provide several way of controlling what kind of data
can be input into a table.
The various control constraints are as follow:-
·
Primary key
·
Not null
·
Unique
·
Default
·
Check
·
Foreign key
Classification of constraints:-
·
Table level
·
Column level
Table level
constraints:-
When a single constraints is applied to more than one column
then it is called table level constraints.
These constraints impact the values being entered for a
combination of column values.
Column level
constraints:-
When a constraints is applied on single column is called
column level constraints.
This constraints effect the values being enter for that
particular column only irrespective of values of other column.
Primary key:-
Primary key can be defined as single column or multiple
column which uniquely identified in a table.
PRIMARY KEY=UNIQUE+NOT NULL.
COMPOSITE PRIMARY
KEY:-
When a primary key is defined as combination of column.
Primary key defined as multiple column is called composite primary
key.
NOTES:-
A table can have one and only one primary key.
Primary key unique as well as not null values.
Unique implies duplicate values can’t be entered.
Not null implies values can’t be unknown (null) for any of
the record.
Primary key column
are automatically indexed.
NOT NULL:-
When a specifying NOT NULL as constraints mean that NULL
values can’t be insert for that particular field although duplicate values for
that column can be insert.
UNIQUE:-
Unique implies duplicate values for the same field can’t be
entered but NULL values can be inserted for that column.
Since two null values can’t be compared therefore unique constraints
does not prevent from supplying NULL values.
DEFAULT:-
If we do not specify a values for column in the insert statement,
then the values specify in default clause gets inserted.
CHECK
CONSTRAINTS:-
Check constraints allow verifying the values being supplied
against specific condiation.
Note that data will be case sensitive.
SQL> create table student_check
2 (
3 rollno number(4),
4 collage varchar2(30) check (collage in
('ime','its','niit')),
5 mark number(5) check (mark >=0 and mark
<=100)
6 );
Table created.
SQL> insert into student_check(1,'ime',50);
insert into student_check(1,'ime',50)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword.
SQL> insert into student_check values(1,'ime',50);
1 row created.
SQL> insert into
student_check values(1,'test',30);
insert into student_check
values(1,'test',30)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0011140) violated
SQL> insert into
student_check values(1,'its',60);
1 row created.
SQL> insert into
student_check values(1,'niit',200);
insert into
student_check values(1,'niit',200)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0011141) violated
Foreign key:-
Assume that when we have two table student primary and student
foreign.
·
The roll no and marks for a student can be
entered in details table only when the specified roll no exit in the student
primary table.
·
The roll
no from the master table can’t be deleted if the corresponding roll no exit in
the foreign table.
SQL> create table
student_primary(rollno number(4) primary key,name varchar2(30),age number(3),address
varchar2(20));
Table created.
SQL> create table student_foreign(rollno number(4)
references student_primary(rollno),subject varchar2(20),marks number(3));
Table created.
SQL> create table student_foreignkey
(
rollno number(4),
subject varchar2(20),
marks number(3),
foreign key (rollno) references student_primary(rollno)
);
Table created.
SQL> insert into student_primary
values(1,'alam',55,'patna');
1 row created.
SQL> insert into
student_primary values(2,'praveen',26,'gurgaon');
1 row created.
SQL> insert into student_foreign values(1,'java',30);
1 row created.
SQL> insert into student_foreign values(2,'game',60);
1 row created.
SQL> insert into
student_foreign values(3,'sql',80);
insert into
student_foreign values(3,'sql',80)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0011143)
violated - parent key not
Found
Notes:-
·
A foreign key can refer to a primary key only.
·
A table can have multiple foreign key referring
to different table for different columns.
·
A table can have only one primary key.
·
Defining foreign key established a PARENT/CHILD
relationship between the two tables.
DATA DICTONARY/META-DATA:-
Oracle maintain the information about the various object
like table in the form table.
SQL> select * from tab;
TNAME
TABTYPE
CLUSTERID
------------------------------ ------- -
DEPT TABLE
EMP TABLE
SALGRADE TABLE
STUDENT
TABLE
STUDENT_CHECK TABLE
STUDENT_CLLAGE
TABLE
STUDENT_DEFAULT
TABLE
ADDING, REMOVING
AND ALTERING CONSTRAINTS:-
The constraints we applied at the time of table creation can
also be specified after the table has been created.
Adding primary key
after table creation:-
Suppose we forget to specify primary key at the time of
table creation.
SQL> create table salary(id number(4),name varchar2(20));
Table created.
SQL> desc salary;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID
NUMBER(4)
NAME
VARCHAR2(20)
SQL> alter table salary add primary key (id);
Table altered.
SQL> desc salary;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID NOT NULL NUMBER(4)
NAME VARCHAR2(20)
SQL> select constraint_name,constraint_type,table_name
from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ -
------------------------------
SYS_C0011134 P COMPOSTIVE_PRIMARY_KEY
SYS_C0011139 U STUDENT_CLLAGE
SYS_C0011142 P STUDENT_PRIMARY
SYS_C0011154 P SALARY
SQL> select constraint_name,table_name,column_name from
user_cons_columns where constraint_name='SYS_C0011154';
CONSTRAINT_NAME
TABLE_NAME COLUMN_NAME
-------------------- ---------- --------------------
SYS_C0011154
SALARY ID
REMOVING PRIMARY
KEY:-
SQL> alter table salary drop primary key;
Table altered.
SPECIFYING USER
DEFINE CONSTRAINTS NAME:-
Oracle automatically assign a unique constraint name to each
and constraints applied starting with ‘SYS_C’.
Oracle also provide a feature also by we can assign our own
defined constraints name.
SQL> create table constraint(id number(4),name
varchar2(20),address varchar2(20),constraint pk_constraint_id primary key
(id));
Table created.
SQL> alter table constraint drop primary key;
Table altered.
SQL> alter table constraint add constraint pk_constraint_id primary key (id);
Table altered.
SQL> alter table constraint drop constraint pk_constraint_id primary key (id);
SQL> alter table
constraint drop constraint
pk_constraint_id;
Table altered.
SQL>
ENABLING AND
DISABLING CONSTRAINTS:-
Oracle provide some special feature enable and disable
constraint instead of dropping them permanently.
SQL> desc constraint;
Name
Null? Type
-----------------------------------------------------------------
-------- --------------------------------------------
ID
NOT NULL NUMBER(4)
NAME VARCHAR2(20)
ADDRESS
VARCHAR2(20)
SQL> alter table constraint disable constraint pk_constraint_id;
Table altered.
SQL> desc constraint;
Name Null? Type
-----------------------------------------------------------------
-------- --------------------------------------------
ID
NUMBER(4)
NAME
VARCHAR2(20)
ADDRESS
VARCHAR2(20)
SQL> select
constraint_name,constraint_type,table_name,status from user_constraints;
CONSTRAINT_NAME C
TABLE_NAME STATUS
-------------------- - ---------- --------
PK_CONSTRAINT_ID P
CONSTRAINT DISABLED
ENABLING
CONSTRAINTS:-
SQL> alter table constraint enable constraint pk_constraint_id;
Table altered.
SQL> desc constraint;
Name Null? Type
-----------------------------------------------------------------
-------- --------------------------------------------
ID
NOT NULL NUMBER(4)
NAME
VARCHAR2(20)
ADDRESS
VARCHAR2(20)
SQL> select
constraint_name,constraint_type,table_name,status from user_constraints;
CONSTRAINT_NAME C
TABLE_NAME STATUS
-------------------- - ---------- --------
PK_CONSTRAINT_ID P
CONSTRAINT ENABLED
No comments:
Post a Comment