AIM
To work on various constraints in DBMS
CONSTRAINTS
Constraints are part of the table definition that limits and restriction on the value
entered into its columns.
TYPES OF CONSTRAINTS:
1) Primary key
2) Foreign key/references
3) Check
4) Unique
5) Not null
6) Null
7) Default
CONSTRAINTS CAN BE CREATED IN THREE WAYS:
1) Column level constraints
2) Table level constraints
3) Using DDL statements-alter table command
OPERATION ON CONSTRAINT:
i) ENABLE
ii) DISABLE
iii) DROP
ALGORITHM
STEP 1: Start
STEP 2: Create the table with its essential attributes.
STEP 3: Execute Commands with different constraints and extract information from the table.
STEP 4: Stop
QUERIES
CONSTRAINTS
PRIMARY KEY CONSTRAINTS:
SQL>create table customer(cusno number(5) primary key, custname varchar(10), salary number(6,2),city varchar(8));
SQL> insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’);
Enter value for cusno: 1
Enter value for custname: a
Enter value for cussal: 5000
Enter value for cuscity: chennai
old 1: insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’)
new 1: insert into customer values(1,’a’,5000,’chennai’)
1 row created.
SQL> /
Enter value for cusno: 2
Enter value for custname: b
Enter value for cussal: 6000
Enter value for cuscity: mumbai
old 1: insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’)
new 1: insert into customer values(2,’b’,6000,’mumbai’)
1 row created.
SQL> /
Enter value for cusno: 3
Enter value for custname: c
Enter value for cussal: 7000
Enter value for cuscity: delhi
old 1: insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’)
new 1: insert into customer values(3,’c’,7000,’delhi’)
1 row created.
SQL> /
Enter value for cusno: 3
Enter value for custname: g
Enter value for cussal: 8000
Enter value for cuscity: chennai
old 1: insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’)
new 1: insert into customer values(3,’g’,8000,’chennai’)
insert into customer values(3,’g’,8000,’chennai’)
ERROR at line 1:
ORA-00001: unique constraint (KUMARM.SYS_C008128) violated
SQL> select *from customer;
CUSNO CUSTNAME SALARY CITY
———- ———- ———- ——–
1 a 5000 chennai
2 b 6000 mumbai
3 c 7000 delhi
UNIQUE KEY CONSTRAINTS
SQL> create table customeruniq(cusno number(4) unique, cusname varchar(6));
Table created.
SQL> insert into customeruniq values(&no, ‘&cusname’);
Enter value for no: 1
Enter value for cusname: a
old 1: insert into customeruniq values(&no, ‘&cusname’)
new 1: insert into customeruniq values(1, ‘a’)
1 row created.
SQL> /
Enter value for no: 2
Enter value for cusname: b
old 1: insert into customeruniq values(&no, ‘&cusname’)
new 1: insert into customeruniq values(2, ‘b’)
1 row created.
SQL> /
Enter value for no: null
Enter value for cusname: c
old 1: insert into customeruniq values(&no, ‘&cusname’)
new 1: insert into customeruniq values(null, ‘c’)
1 row created.
SQL> /
Enter value for no: 2
Enter value for cusname: k
old 1: insert into customeruniq values(&no, ‘&cusname’)
new 1: insert into customeruniq values(2, ‘k’)
insert into customeruniq values(2, ‘k’)
*ERROR at line 1:
ORA-00001: unique constraint (KUMARM.SYS_C008133) violated
SQL> SELECT *FROM customeruniq;
CUSNO CUSNAM
——— ———
1 a
2 b
c
CHECK CONSTRAINTS:
SQL> create table customercheck(cusno number(5) ,cusname varchar(4), sal number(5) , check(sal>5000));
Table created.
SQL> insert into customercheck values(&cusno,’&custname’,&cussal);
Enter value for cusno: 2
Enter value for custname: a
Enter value for cussal: 6000
old 1: insert into customercheck values(&cusno,’&custname’,&cussal)
new 1: insert into customercheck values(2,’a’,6000)
1 row created.
SQL> /
Enter value for cusno: 1
Enter value for custname: b
Enter value for cussal: 8000
old 1: insert into customercheck values(&cusno,’&custname’,&cussal)
new 1: insert into customercheck values(1,’b’,8000)
1 row created.
SQL> /
Enter value for cusno: 3
Enter value for custname: c
Enter value for cussal: 3000
old 1: insert into customercheck values(&cusno,’&custname’,&cussal)
new 1: insert into customercheck values(3,’c’,3000)
insert into customercheck values(3,’c’,3000)
*
ERROR at line 1:
ORA-02290: check constraint (KUMARM.SYS_C008129) violated
Enter value for cusno: 1
Enter value for custname: a
Enter value for cussal: 4000
old 1: insert into customercheck values(&cusno,’&custname’,&cussal)
new 1: insert into customercheck values(1,’a’,4000)
insert into customercheck values(1,’a’,4000)
*
ERROR at line 1:
ORA-02290: check constraint (KUMARM.SYS_C008129) violated
SQL> select *from customercheck;
CUSNO CUSN SAL
——— ——- ———-
2 a 6000
1 b 8000
NULL AND NOT NULL CONSTRIANTS:
create table customernull (cusno number(5), cusname varchar(5),cusal number(5) not null, cusph number(10) null)
SQL> /
Enter value for cusno: 1
Enter value for cusname: a
Enter value for cusal: 4000
Enter value for cusph: 123456
old 1: insert into customernull values(&cusno,’&cusname’ ,&cusal,&cusph)
new 1: insert into customernull values(1,’a’ ,4000,123456)
1 row created.
SQL> /
Enter value for cusno: 2
Enter value for cusname: b
Enter value for cusal: 6000
Enter value for cusph: null
old 1: insert into customernull values(&cusno,’&cusname’ ,&cusal,&cusph)
new 1: insert into customernull values(2,’b’ ,6000,null)
1 row created.
SQL> /
Enter value for cusno: 3
Enter value for cusname: c
Enter value for cusal: null
Enter value for cusph: null
old 1: insert into customernull values(&cusno,’&cusname’ ,&cusal,&cusph)
new 1: insert into customernull values(3,’c’ ,null,null)
insert into customernull values(3,’c’ ,null,null)
ERROR at line 1:
ORA-01400: cannot insert NULL into (“KUMARM”.”CUSTOMERNULL”.”CUSAL”)
SQL> select *from customernull;
CUSNO CUSNA CUSAL CUSPH
———- ——– ——– ———-
1 a 4000 12346
2 b 6000
FOREIGN KEY CONSTRIANTS:
SQL> create table parent2( regno number(5) primary key, name varchar(5), mark number(4));
Table created.
SQL> create table child2 ( regno number(5) references parent2(regno), city varchar(5));
Table created.
SQL> insert into parent2 values(®no,’&name’,&mark);
Enter value for regno: 1
Enter value for name: a
Enter value for mark: 100
old 1: insert into parent2 values(®no,’&name’,&mark)
new 1: insert into parent2 values(1,’a’,100)
1 row created.
SQL> /
Enter value for regno: 2
Enter value for name: b
Enter value for mark: 60
old 1: insert into parent2 values(®no,’&name’,&mark)
new 1: insert into parent2 values(2,’b’,60)
1 row created.
SQL> /
Enter value for regno: 3
Enter value for name: c
Enter value for mark: 78
old 1: insert into parent2 values(®no,’&name’,&mark)
new 1: insert into parent2 values(3,’c’,78)
1 row created.
SQL> select *from parent2 ;
REGNO NAME MARK
——— ——– ———-
1 a 100
2 b 60
3 c 78
4 d 45
5 e 56
SQL> insert into child2 values(®no,’&city’);
SQL> /
Enter value for regno: 1
Enter value for city: chn
old 1: insert into child2 values(®no,’&city’)
new 1: insert into child2 values(1,’chn’)
1 row created.
SQL> /
Enter value for regno: 2
Enter value for city: mum
old 1: insert into child2 values(®no,’&city’)
new 1: insert into child2 values(2,’mum’)
1 row created.
SQL> /
Enter value for regno: 3
Enter value for city: del;
old 1: insert into child2 values(®no,’&city’)
new 1: insert into child2 values(3,’del;’)
1 row created.
SQL> /
Enter value for regno: 7
Enter value for city: chn
old 1: insert into child2 values(®no,’&city’)
new 1: insert into child2 values(7,’chn’)
insert into child2 values(7,’chn’)
ERROR at line 1:
ORA-02291: integrity constraint (KUMARM.SYS_C008135) violated – parent key not found
CONSTRAINTS:
create table constall ( regno number(5) primary key, name varchar(5) not null, mark number(5) check(mark<=100),phno number(10) unique)
Table created.
RESULT
Thus various constraints were created for various tables and output was verified.
