My Profile
Active Members
TodayLast 7 Days
more...
Awards & Gifts
Online Exams
Fresher Jobs
Our fresher job section is exclusively for fresh graduates! Find jobs for freshers in major Indian
cities including Bangalore, Chennai, Hyderabad, Pune or Kochi
Resources
Find educational articles, blogs, discussion threads and other resources.
Colleges
Find details about any college in India or search for courses.
|
Resources » Articles/Knowledge Sharing » Education »
Constraints
|
CONSTRAINTS
1.PRIMARY KEY CONSTRAINTS:
SQL> Create table student (regno number (5) primary key, name varchar2 (10), area varchar2 (10));
Table created.
SQL> insert into student values (®no,'&name','&area');
Enter value for regno: 101
Enter value for name: arul
Enter value for area: kk nagar
old 1: insert into student values(®no,'&name','&area')
new 1: insert into student values(101,'arul','kk nagar')
1 row created.
SQL> /
Enter value for regno: 102
Enter value for name: ashok
Enter value for area: jj nagar
old 1: insert into student values(®no,'&name','&area')
new 1: insert into student values(102,'ashok','jj nagar')
1 row created.
SQL> /
Enter value for regno: 103
Enter value for name: saran
Enter value for area: aa nagar
old 1: insert into student values(®no,'&name','&area')
new 1: insert into student values(103,'saran','aa nagar')
1 row created.
SQL> /
Enter value for regno: 104
Enter value for name: kan
Enter value for area: bb nagar
old 1: insert into student values(®no,'&name','&area')
new 1: insert into student values(104,'kan','bb nagar')
1 row created.
SQL> /
Enter value for regno: 101
Enter value for name: sam
Enter value for area: gg nagar
old 1: insert into student values(®no,'&name','&area')
new 1: insert into student values(101,'sam','gg nagar')
insert into student values(101,'sam','gg nagar')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C00824) violated
SQL> /
Enter value for regno: null
Enter value for name: kumar
Enter value for area: ee nagar
old 1: insert into student values(®no,'&name','&area')
new 1: insert into student values(null,'kumar','ee nagar')
insert into student values(null,'kumar','ee nagar')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT"."REGNO")
2.FOREIGN KEY CONSTRAINTS:
SQL> Select * from student;
REGNO NAME AREA
--------- ---------- ----------
101 arul kk nagar
102 ashok jj nagar
103 saran aa nagar
104 kan bb nagar
SQL> Create table departments (dept varchar2 (10), regno number (5) references student (regno));
Table created.
SQL> insert into departments values ('&dept', ®no);
Enter value for dept: it
Enter value for regno: 101
old 1: insert into departments values('&dept',®no)
new 1: insert into departments values('it',101)
1 row created.
SQL> /
Enter value for dept: ece
Enter value for regno: 102
old 1: insert into departments values('&dept',®no)
new 1: insert into departments values('ece',102)
1 row created.
SQL> /
Enter value for dept: eee
Enter value for regno: 103
old 1: insert into departments values('&dept',®no)
new 1: insert into departments values('eee',103)
1 row created.
SQL> /
Enter value for dept: cse
Enter value for regno: 107
old 1: insert into departments values('&dept',®no)
new 1: insert into departments values('cse',107)
insert into departments values('cse',107)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C00825) violated - parent key not found
3.UNIQUE KEY CONSTRAINTS:
SQL> Create table eecons1 (empno number (3) unique, name char (10), dept char (3));
Table created.
SQL> insert into eecons1 values (&empno,'&ename','&dept');
Enter value for empno: 100
Enter value for ename: preethi
Enter value for dept: cse
old 1: insert into eecons1 values(&empno,'&ename','&dept')
new 1: insert into eecons1 values(100,'preethi','cse')
1 row created.
SQL> /
Enter value for empno: 101
Enter value for ename: rekha
Enter value for dept: ece
old 1: insert into eecons1 values(&empno,'&ename','&dept')
new 1: insert into eecons1 values(101,'rekha','ece')
1 row created.
SQL> /
Enter value for empno: 100
Enter value for ename: swathi
Enter value for dept: it
old 1: insert into eecons1 values(&empno,'&ename','&dept')
new 1: insert into eecons1 values(100,'swathi','it')
insert into eecons1 values(100,'swathi','it')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C00826) violated
SQL> /
Enter value for empno: null
Enter value for ename: swathi
Enter value for dept: ece
old 1: insert into eecons1 values(&empno,'&ename','&dept')
new 1: insert into eecons1 values(null,'swathi','ece')
1 row created.
4.NOT NULL CONSTRAINTS:
SQL> Create table eecons2 (empno number (3) not null, name char (10), dept char(3));
Table created.
SQL> insert into eecons2 values(&empno,'&ename','&dept');
Enter value for empno: 100
Enter value for ename: preethi
Enter value for dept: cse
old 1: insert into eecons2 values(&empno,'&ename','&dept')
new 1: insert into eecons2 values(100,'preethi','cse')
1 row created.
SQL> /
Enter value for empno: null
Enter value for ename: swathi
Enter value for dept: ece
old 1: insert into eecons2 values(&empno,'&ename','&dept')
new 1: insert into eecons2 values(null,'swathi','ece')
insert into eecons2 values(null,'swathi','ece')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EECONS1"."EMPNO")
5.CHECK CONSTRAINTS:
SQL> Create table depptt(dept char(3),salary number(7,2)check (salary>10000 and salary<99999));
Table created.
SQL> insert into depptt values ('&dept',&salary);
Enter value for dept: cse
Enter value for salary: 88888.88
old 1: insert into depptt values('&dept',&salary)
new 1: insert into depptt values('cse',88888.88)
1 row created.
SQL> /
Enter value for dept: ece
Enter value for salary: 99999.99
old 1: insert into depptt values('&dept',&salary)
new 1: insert into depptt values('ece',99999.99)
insert into depptt values('ece',99999.99)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SSAL) violated
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|
Advertise Here
|