EX.No: 3 CONSTRAINTS
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.