Constraint in oracle with example
Oracle constraints are means in the process of defining some conditions about the database that must remain true while inputting/modifying/deleting data in the database.
These Oracle constraints can be attribute-based (column), tuple-based (table), key based and referential integrity based.
As Oracle views are always dynamically generated from their base tables, so the view can not contain constraints.
If there is a violation of the constraints caused by some actions performed on the database, then the Oracle constraints aborts the action accordingly. The Oracle implementation of constraints differs from the SQL implementation of these constraints.
Different type of constraints
1.UNIQUE CONSTRAINT /CANDIDATE KEY
A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns,
SQL> create table unique_sample(name varchar(15) unique,design varchar(15));
Table created.
SQL> insert into unique_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: GM
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Mahesh P','GM')
1 row created.
SQL> /
Enter value for name: Manoj
Enter value for design: clerk
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Manoj','clerk')
1 row created.
SQL> /
Enter value for name: Mahesh P
Enter value for design: peon
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Mahesh P','peon')
insert into unique_sample values('Mahesh P','peon')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.SYS_C005359) violated
Here note down the system generated constraint name SYS_C005359
The main data dictionary views for constraints are
USER_CONSTRAINTS - Constraint definitions on user's own tables
ALL_CONSTRAINTS - Constraint definitions on accessible tables
DBA_CONSTRAINTS - Constraint definitions on all tables
If you note given any name to constraint oracle will automatically assign a name and
the naming convention is SYS_Cnnnnnn to derive the name of the constraint
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from DBA_CONSTRAINTS
where CONSTRAINT_TYPE='U'and TABLE_NAME='UNIQUE_SAMPLE';
CONSTRAINT_NAME C
------------------------------ -
SYS_C005359 U
The status of CONSTRAINT_TYPE fields are
C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)
Note:-
*You can name the constraint at the time of table creation by
sql>create table unique_sample(name varchar(15) ,design varchar(15),constraint const_uni1 unique(name));
*You can disable the constrait by
SQL> alter table UNIQUE_SAMPLE drop constraint const_uni1 ;
*if you want to recover the changes
SQL> alter table unique_sample add constraint const_unit1 unique(name);
we can add constraint to a table by the above method
if any duplicate values are found , it gives error ,you have to delete the duplicate entry
then execute the above query.
*You can temporary disable the constraint by
SQL>alter table unique_sample disable constraint const_uni1;
Now you can insert as many duplicate entry , but whenever you want to enable constraint
you have to delete all duplicate entries in the table.
2.NOT NULL CONSTRAINT
A NOT NULL constraint prohibits a database value from being null. below is the examle
SQL> create table notnull_sample (name varchar(15) not null , design varchar(15));
Table created.
SQL> insert into notnull_sample values ('&name','&design');
Enter value for name: mahi
Enter value for design: gm
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('mahi','gm')
1 row created.
SQL> insert into notnull_sample values ('&name','&design');
Enter value for name: manu
Enter value for design: ceo
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('manu','ceo')
1 row created.
SQL> insert into notnull_sample values ('&name','&design');
Enter value for name:
Enter value for design: peon
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('','peon')
insert into notnull_sample values ('','peon')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MAHI"."NOTNULL_SAMPLE"."NAME")
3.PRIMARY KEY (UNIQUE key + NOT NULL) CONSTRAINT
A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. below is the example
SQL>create table primary_sample ( name varchar(15),design varchar(15),constraint primary1 primary key(name));
SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: Programmer
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Mahesh P','Programmer')
1 row created.
SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Manoj P
Enter value for design: L D Clerk
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Manoj P','L D Clerk')
1 row created.
SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: UD Clerk
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Mahesh P','UD Clerk')
insert into primary_sample values ('Mahesh P','UD Clerk')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.PRIMARY1) violated
SQL> insert into primary_sample values ('&name','&design');
Enter value for name:
Enter value for design: GM
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('','GM')
insert into primary_sample values ('','GM')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MAHI"."PRIMARY_SAMPLE"."NAME")
3. COMBINED / COMPOSITE PRIMARY KEY
Uniqueness in a composite primary key is determined by combining the values in all
columns of the compositer primary key .If the combination is unique , then the key is
unique .
consider the following example
I want to create a table named results for storing the result of a students in an engineering
college . There may be a lot of students studying in a particular semester , so the field for semester(here semno) should not be a primary key. Also we have to store the results of a particular student for all semester ,so the filed for student(here rollno) also not to be a primary key. Here we have to met only one condition , both the filed rollno and semno should not repeat simultaneously
SQL> create table results (
2 semno number(1),
3 rollno number(5),
4 english number(3),
5 science number(3),
6 maths number(3),
7 hindi number(3),
8 constraint result_pk primary key(semno,rollno));
Table created.
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 1
Enter value for rollno: 1001
Enter value for english: 78
Enter value for science: 85
Enter value for maths: 85
Enter value for hindi: 86
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('1' , '1001' ,'78' , '85' , '85' , '86')
1 row created.
SQL>
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 2
Enter value for rollno: 1001
Enter value for english: 80
Enter value for science: 85
Enter value for maths: 92
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('2' , '1001' ,'80' , '85' , '92' , '89')
1 row created.
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 1
Enter value for rollno: 1002
Enter value for english: 69
Enter value for science: 89
Enter value for maths: 91
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('1' , '1002' ,'69' , '89' , '91' , '89')
1 row created.
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 3
Enter value for rollno: 1001
Enter value for english: 85
Enter value for science: 86
Enter value for maths: 87
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('3' , '1001' ,'85' , '86' , '87' , '89')
1 row created.
SQL> select * from results;
SEMNO ROLLNO ENGLISH SCIENCE MATHS HINDI
---------- ---------- ---------- ---------- ---------- ----------
1 1001 78 85 85 86
2 1001 80 85 92 89
1 1002 69 89 91 89
3 1001 85 86 87 89
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 2
Enter value for rollno: 1001
Enter value for english: 89
Enter value for science: 98
Enter value for maths: 87
Enter value for hindi: 87
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('2' , '1001' ,'89' , '98' , '87' , '87')
insert into results values ('2' , '1001' ,'89' , '98' , '87' , '87')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.RESULT_PK) violated
Note : Oracle enforces uniqueness on the combination of values in semno and rollno fields , and hence our 5th insert statement causes an error because it is an exact duplicate of the first.
Oracle constraints are means in the process of defining some conditions about the database that must remain true while inputting/modifying/deleting data in the database.
These Oracle constraints can be attribute-based (column), tuple-based (table), key based and referential integrity based.
As Oracle views are always dynamically generated from their base tables, so the view can not contain constraints.
If there is a violation of the constraints caused by some actions performed on the database, then the Oracle constraints aborts the action accordingly. The Oracle implementation of constraints differs from the SQL implementation of these constraints.
Different type of constraints
1.UNIQUE CONSTRAINT /CANDIDATE KEY
A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns,
SQL> create table unique_sample(name varchar(15) unique,design varchar(15));
Table created.
SQL> insert into unique_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: GM
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Mahesh P','GM')
1 row created.
SQL> /
Enter value for name: Manoj
Enter value for design: clerk
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Manoj','clerk')
1 row created.
SQL> /
Enter value for name: Mahesh P
Enter value for design: peon
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Mahesh P','peon')
insert into unique_sample values('Mahesh P','peon')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.SYS_C005359) violated
Here note down the system generated constraint name SYS_C005359
The main data dictionary views for constraints are
USER_CONSTRAINTS - Constraint definitions on user's own tables
ALL_CONSTRAINTS - Constraint definitions on accessible tables
DBA_CONSTRAINTS - Constraint definitions on all tables
If you note given any name to constraint oracle will automatically assign a name and
the naming convention is SYS_Cnnnnnn to derive the name of the constraint
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from DBA_CONSTRAINTS
where CONSTRAINT_TYPE='U'and TABLE_NAME='UNIQUE_SAMPLE';
CONSTRAINT_NAME C
------------------------------ -
SYS_C005359 U
The status of CONSTRAINT_TYPE fields are
C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)
Note:-
*You can name the constraint at the time of table creation by
sql>create table unique_sample(name varchar(15) ,design varchar(15),constraint const_uni1 unique(name));
*You can disable the constrait by
SQL> alter table UNIQUE_SAMPLE drop constraint const_uni1 ;
*if you want to recover the changes
SQL> alter table unique_sample add constraint const_unit1 unique(name);
we can add constraint to a table by the above method
if any duplicate values are found , it gives error ,you have to delete the duplicate entry
then execute the above query.
*You can temporary disable the constraint by
SQL>alter table unique_sample disable constraint const_uni1;
Now you can insert as many duplicate entry , but whenever you want to enable constraint
you have to delete all duplicate entries in the table.
2.NOT NULL CONSTRAINT
A NOT NULL constraint prohibits a database value from being null. below is the examle
SQL> create table notnull_sample (name varchar(15) not null , design varchar(15));
Table created.
SQL> insert into notnull_sample values ('&name','&design');
Enter value for name: mahi
Enter value for design: gm
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('mahi','gm')
1 row created.
SQL> insert into notnull_sample values ('&name','&design');
Enter value for name: manu
Enter value for design: ceo
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('manu','ceo')
1 row created.
SQL> insert into notnull_sample values ('&name','&design');
Enter value for name:
Enter value for design: peon
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('','peon')
insert into notnull_sample values ('','peon')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MAHI"."NOTNULL_SAMPLE"."NAME")
3.PRIMARY KEY (UNIQUE key + NOT NULL) CONSTRAINT
A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. below is the example
SQL>create table primary_sample ( name varchar(15),design varchar(15),constraint primary1 primary key(name));
SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: Programmer
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Mahesh P','Programmer')
1 row created.
SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Manoj P
Enter value for design: L D Clerk
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Manoj P','L D Clerk')
1 row created.
SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: UD Clerk
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Mahesh P','UD Clerk')
insert into primary_sample values ('Mahesh P','UD Clerk')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.PRIMARY1) violated
SQL> insert into primary_sample values ('&name','&design');
Enter value for name:
Enter value for design: GM
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('','GM')
insert into primary_sample values ('','GM')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MAHI"."PRIMARY_SAMPLE"."NAME")
3. COMBINED / COMPOSITE PRIMARY KEY
Uniqueness in a composite primary key is determined by combining the values in all
columns of the compositer primary key .If the combination is unique , then the key is
unique .
consider the following example
I want to create a table named results for storing the result of a students in an engineering
college . There may be a lot of students studying in a particular semester , so the field for semester(here semno) should not be a primary key. Also we have to store the results of a particular student for all semester ,so the filed for student(here rollno) also not to be a primary key. Here we have to met only one condition , both the filed rollno and semno should not repeat simultaneously
SQL> create table results (
2 semno number(1),
3 rollno number(5),
4 english number(3),
5 science number(3),
6 maths number(3),
7 hindi number(3),
8 constraint result_pk primary key(semno,rollno));
Table created.
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 1
Enter value for rollno: 1001
Enter value for english: 78
Enter value for science: 85
Enter value for maths: 85
Enter value for hindi: 86
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('1' , '1001' ,'78' , '85' , '85' , '86')
1 row created.
SQL>
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 2
Enter value for rollno: 1001
Enter value for english: 80
Enter value for science: 85
Enter value for maths: 92
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('2' , '1001' ,'80' , '85' , '92' , '89')
1 row created.
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 1
Enter value for rollno: 1002
Enter value for english: 69
Enter value for science: 89
Enter value for maths: 91
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('1' , '1002' ,'69' , '89' , '91' , '89')
1 row created.
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 3
Enter value for rollno: 1001
Enter value for english: 85
Enter value for science: 86
Enter value for maths: 87
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('3' , '1001' ,'85' , '86' , '87' , '89')
1 row created.
SQL> select * from results;
SEMNO ROLLNO ENGLISH SCIENCE MATHS HINDI
---------- ---------- ---------- ---------- ---------- ----------
1 1001 78 85 85 86
2 1001 80 85 92 89
1 1002 69 89 91 89
3 1001 85 86 87 89
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 2
Enter value for rollno: 1001
Enter value for english: 89
Enter value for science: 98
Enter value for maths: 87
Enter value for hindi: 87
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('2' , '1001' ,'89' , '98' , '87' , '87')
insert into results values ('2' , '1001' ,'89' , '98' , '87' , '87')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.RESULT_PK) violated
Note : Oracle enforces uniqueness on the combination of values in semno and rollno fields , and hence our 5th insert statement causes an error because it is an exact duplicate of the first.
No comments:
Post a Comment